Sheet Limit??? (Excel 2000 or 2003)

Topic

New Reply

Hi all … I need help again (spent most of yesterday on this ) …

I have code that basically, if conditions are met, copies a given sheet in the workbook and gives it a name. It has only added about 50 sheets when it crashes on the CopyBefore line below … And … I went into the excel file (with all the newly created sheets) and cannot manually perform a copy on the sheet either. So, my question (to start with) is there a limit on the number of sheets a workbook can have?

I’m kind of embarrassed to show my code since I am self-taught, but here’s a little of the code anyways …

For A = 1 To AnimalCT
Sheets(“Input-Group Info”).Select
AnimalID = Range(AnimalColLetter & 25 + A).Value
Sheets(“Generic Matrix AnimalID”).Visible = True
Sheets(“Generic Matrix AnimalID”).Select
Sheets(“Generic Matrix AnimalID”).Copy Before:=Sheets(1)
Sheets(“Generic Matrix AnimalID (2)”).Select
Sheets(“Generic Matrix AnimalID (2)”).Name = “Plasma ” & AnimalID

Again, any help/guidance is appreciated deeply
–cat

Reply | Quote

Replies

No need to be embarrassed! This forum is for all kinds of users, not just for diehard professionals.

First, check out post 519,001 to see if the problem mentioned there applies to your situation.

Otherwise, it may help to save, close and reopen the workbook periodically in the loop – see Copying worksheet programmatically causes run-time error 1004 in Excel

Reply | Quote

Thank you, Hans.

It is not the first post (re the Sheet11111111) … however, it is excactly the Runtime Error 1004 issue.
I will try saving the file during this. If you don’t hear from me again … it will be working.

Thank you, again.
–cat

Reply | Quote

Hi Hans (and others) …

I used the workaround solution noted in your link regarding Runtime Error 1004 …
This workaround has you create an excel template with that sheet in it (the sheet I was trying to ‘copy’ in the first place)
And, you perform a “Sheets.Add Type:=pathfilename …. instead of the copy.

Well, it works fine. No crashes and completes the job.
But, it is very, very slow because it appears to be performing a virus scan (so says the Status Bar) each time it adds that sheet via the Type method.
In my (close to an upper-limit) case, the sheet was added via the Type method 144 times and took about 10 minutes …

Perhaps, I can turn off “Virus Scan” for this particular template ??? … but, that just sounds wrong. And, if it was easy, it would also sound scary.

Any suggestions … otherwise I will live with this performance issue since it is only used once per project ..

Thanks,
–cat

Reply | Quote

Do you have Norton Anti Virus? If so, make sure that the Office Plug-in is disabled:
– Activate the main NAV window.
– Click Options.
– Click Miscellaneous in the Other section.
– Clear the check box labeled Enable Office Plug-in.
– Click OK.
As long as you leave the normal protection on, disabling the Office plug-in is safe. It is known to cause slow performance.

Reply | Quote

That did it !!!
You’re wonderful
–cat

Reply | Quote