I'm working on an application (in Excel, with VBA code behind it). Part of
the process of building this was creating a "template" worksheet (with the
code already with it, although commented-out), and then creating a function
that will duplicate that "template" as needed (ultimately 600-700 additional
worksheets added to the workbook).


The code I have to do that is basically a WHILE loop, referencing data in
another worksheet, incrementing integer intLoop each time, and ending when
it runs out of data on that worksheet. There is also an integer
intBeforeSheet that tells it where to insert the copies. The code to
actually copy the template is as follows:

Sheets("WS_Template").Select
Sheets("WS_Template").Copy Befo=Sheets(intBeforeSheet +
intLoop)


It seems to have been working for the most part, until today. Today, it
seems when it hits the point that there's a total of 360 worksheets, Excel
crashes. Actually, it gives me the following:

"Run-time error '-21474147848 (80010108)':

Automation error
The object invoked has disconnected from its clients


And only gives me the option of End (not Continue or Debug - those are
greyed out). When I End, it seems to end (I can still work with the VBA
code), but when I go to the worksheet itself, I can't do anything.


Since I managed to narrow it down to when it hits worksheet #360, I tried
ending the automatic process when it hits 359, and then manually copying the
template worksheet. When I did that, it crashed Excel.


I'm on a 1.7GHz P4 Win 2000 (pro) box, with 512MB of memory. When saved to
disk (in Excel XP/2002 format), the file is 15MB in size (saved with just
over 300 worksheets). When I check the Windows Task Manager, even with 359
worksheets (at the crash), it indicates that Excel is only using 101MB of
memory.

So I doubt that it's an out-of-memory issue. But I could be wrong.


Either way, I'm in a major bind. If I can figure out that it's a memory
problem, I might be able to get more memory for this computer. But if it's
not, I'm at a loss to figure out what to try next.



Any ideas?

I'm rather stuck right now, as this is a project that needs to be done in
the next few weeks, so re-writing it in another platform (non-Excel) isn't
exactly an option. Unfortunately, limiting how many worksheets isn't exactly
an option either.