error when copying large numbers of sheets into 1 workbook

[claudehenri]

Hi

I am copying a worksheet may time over into the same workbook. Once I reach 59 sheets (total) i get an error

Run-time error '1004':
Copy method of worksheet failed

the line of code i am using is

Worksheets("Boom").Copy After:=Worksheets(AfterSht)

"boom" is the sheet i have already copied 39 times
and AfterSht is the name of the last sheet that i copied.

don't know why this is happening. any ideas anyone?

I have had this occur before on occasion, with fewer sheets, and have been able to continue after having restarted xl. But even if restart the computer this still occurs.

Claude-Henri

 

[xlbo]

Have a look in the VBE (within the properties box) at the code names for the sheets and tell me what you see

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

darn over sensitive [RETURN] key

I was going to say, I believe you will see something like

SheetName
SheetName1
SheetName11
SheetName111
SheetName1111
SheetName11111
SheetName111111
SheetName1111111
SheetName11111111

etc etc

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[claudehenri]

Yeh I see something similar.

Not with the sheet that is crashing the code ("Boom"). But with another sheet ("Wave") has which I have copied (t an earlier stage)in a similar fashion to "Boom"

Sheet2112 (Wave 1)
Sheet2113 (Fly Wave 5)
Sheet2114 (Fly Wave 4)
Sheet2115 (Fly Wave 3)
Sheet2116 (Fly Wave 2)
Sheet2117 (Fly Wave 1)
Sheet2118 (Wave 5)
Sheet2119 (Wave 4)
Sheet2120 (Wave 3)
Sheet2121 (Wave 2)

Hope you know what to do

C-H.

 

[claudehenri]

I have inserted a new sheet and copied the format from "Wave" across to the new sheet (Sheet5), I got passed where the problem was occuring before but it happened again, this time when there was a total of 65 sheets.

There was nothing unusual about the sheet names this time.

C-H.

 

[PHV]

Ask the Excel help engine for limitations and take a look at the maximal number of sheets in a workbook.
Seems you have to free some memory and ressources ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[claudehenri]

There error "message" was slightly different this time

Copy method of Worksheet class failed.

C-H.

 

[claudehenri]

Excel help on the net says that number of sheets is limited only by available memory.

When the error occured I insert this extra bit of code to see my memory usage

Temp=Application.MemoryFree
Temp=Application.MemoryUsed
Temp=Application.MemoryTotal

Temp values were

1. 1048576
2. 6548816
3. 7597382

so i've still got 13% available. The sheet i am copying is mostly empty so I think it shouldn't have problems with this.

I also changed the paging file size from 756 to 1512 to see if this helped. No change in the error occured.

Maybe I do need more memory, but I don't think so.

C-H.

 

[xlbo]

Hmmm - what version of XL ?

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[claudehenri]

XL 2003

I have tried taking out the sheet that causes the error into another file and copying the sheet into the same file. I get a few more sheets to copy this way. Still end up with the same error though.

C-H.

 

[bjele]

I am encountering this very problem right now. Back in Excel 97, this was always a "Sheet111111" problem after 127 sheets, but like you - I am encountering this around sheet 51.

Finally, Microsoft has posted a new theory on the matter. They suggest the problem is with having a named range in the workbook.

Two workarounds are suggested here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw

Bill

 

[bjele]

I tried their first workaround - a goofy, hard-coded if statement to stop the process before I was getting the error, then code to save the file with a temporary name, close it, open it, then continue. It seems to work.

If x = 30 Then
TempFile = "C:\temp9999.xls"
On Error Resume Next
Kill (TempFile)
On Error GoTo 0
ActiveWorkbook.SaveAs TempFile
ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open TempFile
End If

 

[claudehenri]

OK I've tried the second work around and I get another error when I trie to save. The reason I've come to my current problem is because I ran into the error caused by #2 work around (inserting a template)

the thread i start for the work around problem is

thread707-1007550 "object disconnected from clients"

http://www.tek-tips.com/viewthread.cfm?qid=1007550&page=1

the insert command works but when you want to save the file XL crashes.

I'll try #1 work around. Its just going to take a while as the file is fairly big (as suggested by the large no of sheets), it doesn't appeal to me very much

Claude-Henri

 

[claudehenri]

I've avoided by problem by opening the template and copying the sheet I want across to the main file. xl doesn't crash when i try to save when doing this.
C-H