Worksheet.Copy() truncates cell contents problem

[Mark Hanley]

I have written a macro which copies a worksheet and then alters some
of the values in the copied sheet.

Everything works fine except that any text cells with more than 255
characters are truncated.

Is there a way I can stop excel doing this?

TIA
Mark.

 

[JLGWhiz]

Maximum column width up through Excel 2003 is 255 characters.

 

[Gary''s Student]

1. Do the worksheet copy first
2. have the macro go back to the original sheet and look for text cells
containing more than 255 characters and copy them individually

 

[Ron de Bruin]

You can do this

After you copy the whole sheet
Copy all cells from the source sheet and copy it in the new sheet

sheets("sheet1").cells.copy destsheet.cells(1)

If you not care about the settings of the sheet settings
You can insert a new sheet and copy all cells in this new sheet