Sheet/Tab Copy Limit?

[MWE]

I am running xl2003 and WinXP. I have a application that copies a number of sheets from Book A to Book B. Although I have been writing VBA code for many years, this is the first time I wrote something that copies more than a half dozen or so sheets. I have run into a limitation regarding how many sheets can be copied from A to B before the process halts with an error. That number is 26.
If B remains open and I try to manually copy a sheet from A to B, the copy fails without any error.

If I close/save B after copying 26 sheets from A, reopen B then try to copy more sheets from A to B, I can copy another 26 before I hit the same wall. The current workaround is to stop copying after each block of 26 sheets, close/save B, reopen B and continue.

Anyone have any idea what is happening here?

[Aflatoon]

It is a long standing bug in Excel. The recommended workaround is either to do what you are doing, or use a template sheet instead of copying sheets.

[frank_m]

HI MWE,

Would this be something you could adapt to your need?
I tested it with about 35 sheets, but my guess is that it would work with a lot more.
[vba]
Sub CopyAlotOfSheets()
'Macro in BookA
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Sheets
'specify sheets that you do not wish to copy, and group the rest.
If Sht.Name <> "Sheet4" _
And Sht.Name <> "Sheet7" _
And Sht.Name <> "Sheet11" Then
Sht.Select False
End If
Next
ActiveWindow.SelectedSheets.Copy Before:=Workbooks("BookB.xls").Sheets(1)
End Sub
[/vba]
Tested using Win XP Home and Excel 2003

[GTO]

It is a long standing bug in Excel. The recommended workaround is either to do what you are doing, or use a template sheet instead of copying sheets.

Hi Aflatoon

I found this: http://support.microsoft.com/kb/210684

But I notice that it states the bug exists when copying sheets from/to the same workbook and the worksheet has a named range.

"This problem can occur when you give the workbook a defined name and then copy the worksheet several times without first saving and closing the workbook, as in the following sample code: ..."

I tried the first code in the article, sure enough, it failed at about 256 sheets copied. After save/close/reopen, I could get it to copy about 102 additional sheets, save/close/open, eighty something more, etc.

But, if I copied a sheet from ThisWorkbook to the destination, I didn't run into any error. (Albeit the code seems to be taxing and slows on my poor ol' laptop)

[VBA]
Sub CopyToOtherWB()
Dim lCount As Long
Dim wb As Workbook

ThisWorkbook.Names.Add Name:="tempRange", RefersTo:="=Sheet1!$A$1"
ThisWorkbook.Save

Application.ScreenUpdating = False
Set wb = Workbooks.Add(xlWBATWorksheet)
wb.SaveAs ThisWorkbook.Path & "\TestFail.xls"
'// Succeeds, but seems to slow down during the course of runtime, as sheet count is//
'// increased. Not timed, but Saving wb after done seem slower than norm for same //
'// size file. //
For lCount = 1 To 375
Sheet1.Copy After:=wb.Worksheets(1)
Next
Application.ScreenUpdating = True
End Sub
[/VBA]

Mark

[Aflatoon]

My recollection is that the existence of a named range is immaterial. There is simply a limit to how many times you can copy a sheet.

[wilg]

Ive come across this before. Try on error workbook save, close reopen and run you code again if you want to do it automatically. Only thing is it will prompt you to enable macro each time unless you set your security settings to low