"Andibevan" wrote in message
...
Hi All,
I am using the following code to copy a sheet two sheets to new workbooks
(seperate workbooks):-
sheets("Sheet1").copy
sheets("Sheet2").copy

This does not work as some of the cells contain more than 255 characters.

Hi Andi,

To get around this problem you need a two-step process. First copy the
worksheet itself (and let all cells with more than 255 characters get
truncated), then copy the contents of the worksheet and paste them into the
copy. Here's one way of doing it:

Sub CopySheets()

Dim wkbBook As Workbook
Dim wksSheet As Worksheet

Set wkbBook = ActiveWorkbook

Set wksSheet = wkbBook.Worksheets("Sheet1")
wksSheet.Copy
wksSheet.Cells.Copy ActiveSheet.Range("A1")

Set wksSheet = wkbBook.Worksheets("Sheet2")
wksSheet.Copy
wksSheet.Cells.Copy ActiveSheet.Range("A1")

End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm