vba copy sheet limit (Excel2003)

Topic

New Reply

Hi All

Some time ago I ran into a problem when programmatically copying sheets to another workbook.
There appeared to be a somewhat random limit in how many sheets could be copied.
I’ve tried to search posts but can’t find what I’m looking for.

Any suggestions?

zeddy

Reply | Quote

Replies

Hi Zeddy

In general the number of worksheets allowed in a workbook is only limited by the available memory

Reply | Quote

Hi Jezza

Indeed you are correct.
The number of sheets you can have is dictated to a great extent by available memory.

However, when using VBA to copy a sheet into another workbook you will find that after a while it will stop.
I am using a loop to process sets of data that need to be appended into an external workbook.
The same code running on different systems will stop at different points.
On one of my PCs, I can get to 85 sheets copied before it bombs.

zeddy

Reply | Quote

I think the general workaround is to periodically save the workbook.

Reply | Quote

Hi Rory

I think this was the advice I remember from before.
What do you suggest?
After every 20 sheets? 50 sheets?

zeddy

Reply | Quote

If you are doing the copy in a loop, then I would just insert a save in the loop to do the save each time through the loop. If that slows things down too much, then stick in a counter to only save ever 10th or 20th iteration. If this problem is caused by a memory leak, you could still have problems if you don’t save after every copy.

Reply | Quote

Thanks Legare

I think I will add a loop counter as suggested.
I believe this is a recognised problem with Microsoft Excel but I just don’t seem able to navigate their helpsite and I’ve no idea what happened to their old knowledgebase.

Regards

zeddy

Reply | Quote

There is also another problem when copying worksheets where the code name that Excel assigns to the new sheet gets one character longer for each sheet that is copied until the code name becomes longer than the max allowed. I don’t know if this is the problem that you are having or if saving the workbook will solve the problem. You can tell if this is your problem by looking at the worksheet code names in the VBA project explorer after your copy loop has quit.

Reply | Quote

AFAIK the worksheet codename problem was fixed with Excel XP and with 2000 SR 3.

Reply | Quote

What happens when this moving limit is reached? Do you by any chance get the error “The object invoked has disconnected from its clients”?

If so, try firing a recalc after each copy:

Sheets(“Blah”).Copy
Application.Calculate
‘rest of code

Reply | Quote

I have read a report in another newsgroup that there is an absolute limit to the number of sheets in a workbook.
Both Excel 2003 and Excel 2007 crashed upon adding the 5,448th sheet.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware%5B/url%5D
(Excel Add-ins / Excel Programming)

Reply | Quote

I’m not sure that this is a real limit, but even if it is, one should never need to create that many worksheets – a workbook becomes unwieldy with far fewer worksheets.

Reply | Quote