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

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?

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


"aidan.heritage@virgin.net" 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?
>
>

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

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