Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet Limit??? (Excel 2000 or 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sheet Limit??? (Excel 2000 or 2003)

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

    First, check out <post:=519,001>post 519,001</post:> 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

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Limit??? (Excel 2000 or 2003)

    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

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Limit??? (Excel 2000 or 2003)

    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 ... <ouch>

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sheet Limit??? (Excel 2000 or 2003)

    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.

  6. #6
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Limit??? (Excel 2000 or 2003)

    That did it !!!
    You're wonderful
    --cat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •