Truncated Text

Hi Jennifer,

Yes, in Excel 2000, when you copy a worksheet all cell contents are truncated to 255 characters (i.e., only the first 255 characters are copied). However, if you copy the worksheet CELLS instead of the worksheet, the entire contents of the cells are copied. So you will just need to change your macro to create a new empty worksheet, then copy the cells of interest to the new sheet. I believe code like this will do it:

Dim OldWS As Worksheet
Dim NewWS As Worksheet
Set OldWS = ActiveSheet 'or a specific sheet, like Worksheets("Sheet1")
Set NewWS = Worksheets.Add
OldWS.UsedRange.Copy Destination:=NewWS.[A1]

You could add this to your copy code:

Sub Fit()
With Selection
.WrapText = True
.ShrinkToFit = False
.MergeCells = False
End With
End Sub

And/or designate column width to maximum

Selection.ColumnWidth = 255

Damon

Thank you for responding to my post. I am not very good at writing codes, but I was able figure out enough and insert yours into a macro. It worked. It didn't truncate the text, but it saved the worksheet in the same workbook - I need it to save to a new workbook. Also, the formatting and column widths didn't copy over to the 2nd worksheet.

I appreciate any further help you can give me!

Jennifer