Copying Sheets to New workbooks
 

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.

I want two copy sheet1 and sheet2 to two new workbooks respectively.

I then want to save each of them in the format Sheet_1_MONTH where MONTH is
the month the file is saved in.

Any help or pointers would be helpful.

Thanks

Andi

Copying Sheets to New workbooks
 

Sorry - When I say doesn't work I mean it comes up with error "Run-time
error '-2147352565 (8002000b)': The sheet you are copying has cells that
contain more than 255 characters. When you copy the entire sheet, only the
first 255 characters in each cell are copied.

To copy all of the character, copy the cells to a new sheet instead of
copying the entire sheet."

Having thought about it, maybe a bit of error handling would sort it out.
Not sure though...

"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.

I want two copy sheet1 and sheet2 to two new workbooks respectively.

I then want to save each of them in the format Sheet_1_MONTH where MONTH
is
the month the file is saved in.

Any help or pointers would be helpful.

Thanks

Andi

Copying Sheets to New workbooks
 

"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