Hitting a wall in excel

[hall.jeff]

I know there's some limits in excel related to total number of
worksheets, total different "types" of formatting, probably total
named ranges (although I've never hit that one)... But there must be
some others because we're bumping up against some odd behavior...

We have a sheet that copies off copies of a template sheet and renames
them... the code looks something like this:

Sheets("Sheet1").Copy After:=Sheets(3)

Here's as thorough a description of the actual behavior as I can give:

User adds numerous duplicate tabs. At 35 duplicates (which, if it
matters, is 35 duplicates, 1 template, 4 other sheets and 7 OTHER
templates or 48 total sheets) we start getting two errors...
Copy method of Worksheet class failed
& then
Subscript out of range

I've tracked the errors back to the above line of code. Now it's
important to realize that Excel is also behaving funny at this point.
If I try to manually copy a tab, I do NOT get an error, but nothing
happens (no added sheet shows up) and if I try to record a macro of me
doing that I don't get the relevant line of code recorded in VBA. I
can add a new blank worksheet and that adds fine. I still cannot COPY
an existing worksheet by hand (even one of the blank ones).

I'm at 2996 named ranges but that doesn't seem close to any of the
limits you would expect (already over 2048 and not anywhere near
4096)... copying a template sheet should add any more format types...
so I'm at a loss.

Any advice would be appreciated.

 

[Pete_UK]

The very first post I put up in these groups related to this, and I
was referred by Norman Jones to this post from Rob Bovey:

http://groups.google.com/group/micr...+group:*Excel*+author:rob+author:bovey&rnum=1

It is to do with the number of copy operations, and the amount of
memory you have, as Excel is not very good at memory management. I had
to re-write the macro I was developing at the time so that I recorded
where I was up to in case of a crash, so that when I re-ran the macro
it picked up from where it had left off and carried on until the next
crash. The client I developed the macro for is still using it every
month, and is quite happy to re-run the macro five or six times (for
400+ copies) until it completes.

Hope this helps.

Pete