Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Sheets between workbooks without links | Excel Discussion (Misc queries) | |||
copying workbooks | Excel Worksheet Functions | |||
Copying workbooks | New Users to Excel | |||
Copying from other Workbooks | Excel Discussion (Misc queries) | |||
Copying from several workbooks into one | Excel Programming |