Excel SaveAs Truncates HELP

[detroitpickles]

I have a workbook containing worksheets. I am saving one of these worksheets. When I do it, Excel truncates the cell contents to 255 characters. I looked into it, and it seems like my "ActiveSheet.Copy" line is the problem. I do NOT want to simply "rename" the file, whcih is what the SaveAs would do, so I "copy" the worksheet first, then save that. By doing this, it truncates any cell length to 255.

Here is the code I have:

'start
ThisWorkbook.Worksheets("Package&quot.Activate
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Junk.cps", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'end

Any ideas are greatly appreciated....

 

[lestatdelioncourt]

instead of doing:

activesheet.copy
activeworkbook.saveas .......

do:

activeworkbook.savecopyas

if you do not want to save all sheets. save your master copy, delete the sheets that you do not want then run the above

 

[RobBroekhuis]

The behavior you're seeing is an oddity of Excel VBA. You can get around it by copying the sheet's CONTENTS instead of the sheet itself to a new sheet, e.g.
activesheet.usedrange.copy OtherWorkbook.sheets(1).range("A1&quot
Rob