Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Peculiar Problem when Doing a Sheet.Copy
Excel 2003. I have some code that copies sheet by sheet from one workbook
into another, renaming the sheets as it goes. Even though the source workbook has 80 sheets, the copy never gets past the 58th sheet, then errors out. I have tried using various source workbooks and always bomb at the 58th sheet. It is not a problem with the sheet being copied. Here is the code: For Each sh In TempWb.Sheets 'Copy sh.Copy After:=Workbooks(YTDWbName).Sheets(1) 'Rename YTDWb.ActiveSheet.Name = Mid(YTDWb.ActiveSheet.Name, 1, 27) + "(4)" Next sh What could be my problem? Thanks and God bless for any help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Peculiar Problem when Doing a Sheet.Copy
POSSIBLY it's a memory issue, but IF all sheets are being copied and
renamed, why not change tack and save the workbook AS a different name (thus creating another copy) and rename each sheet in this one? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Peculiar Problem when Doing a Sheet.Copy
Excel seems to have a problem with repetitive copying of sheets.
cnt = TempWb.sheets.count TempWb.Sheets.copy After:=Workbooks(YTDWbName).Sheets(1) for i = 2 to cnt + 1 With Workbooks(YTDWbName).sheets(i) .Name = Mid(.Name, 1, 27) & "(4)" Next sh Next -- Regards, Tom Ogilvy "Chaplain Doug" wrote: Excel 2003. I have some code that copies sheet by sheet from one workbook into another, renaming the sheets as it goes. Even though the source workbook has 80 sheets, the copy never gets past the 58th sheet, then errors out. I have tried using various source workbooks and always bomb at the 58th sheet. It is not a problem with the sheet being copied. Here is the code: For Each sh In TempWb.Sheets 'Copy sh.Copy After:=Workbooks(YTDWbName).Sheets(1) 'Rename YTDWb.ActiveSheet.Name = Mid(YTDWb.ActiveSheet.Name, 1, 27) + "(4)" Next sh What could be my problem? Thanks and God bless for any help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Peculiar Problem when Doing a Sheet.Copy
The sheet copy loop is within another loop that processes more than one
workbook. Thus, the destination workbook is created, then multiple source workbooks are processed, one by one, copying each sheet in the source into the destination. If it were just one workbook, then the saveas and rename would work. What do you mean by a memory issue? -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org " wrote: POSSIBLY it's a memory issue, but IF all sheets are being copied and renamed, why not change tack and save the workbook AS a different name (thus creating another copy) and rename each sheet in this one? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Peculiar Problem when Doing a Sheet.Copy
Thanks Tom. Will Give it a try!
-- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org "Tom Ogilvy" wrote: Excel seems to have a problem with repetitive copying of sheets. cnt = TempWb.sheets.count TempWb.Sheets.copy After:=Workbooks(YTDWbName).Sheets(1) for i = 2 to cnt + 1 With Workbooks(YTDWbName).sheets(i) .Name = Mid(.Name, 1, 27) & "(4)" Next sh Next -- Regards, Tom Ogilvy "Chaplain Doug" wrote: Excel 2003. I have some code that copies sheet by sheet from one workbook into another, renaming the sheets as it goes. Even though the source workbook has 80 sheets, the copy never gets past the 58th sheet, then errors out. I have tried using various source workbooks and always bomb at the 58th sheet. It is not a problem with the sheet being copied. Here is the code: For Each sh In TempWb.Sheets 'Copy sh.Copy After:=Workbooks(YTDWbName).Sheets(1) 'Rename YTDWb.ActiveSheet.Name = Mid(YTDWb.ActiveSheet.Name, 1, 27) + "(4)" Next sh What could be my problem? Thanks and God bless for any help. -- Dr. Doug Pruiett Good News Jail & Prison Ministry www.goodnewsjail.org |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Peculiar Date problem | New Users to Excel | |||
web query peculiar problem | Excel Programming | |||
A peculiar PDF from Excel problem | Excel Discussion (Misc queries) | |||
A peculiar problem | Excel Programming | |||
A peculiar problem | Excel Programming |