Results 1 to 12 of 12
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    vba copy sheet limit (Excel2003)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vba copy sheet limit (Excel2003)

    Hi Zeddy

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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: vba copy sheet limit (Excel2003)

    I think the general workaround is to periodically save the workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: vba copy sheet limit (Excel2003)

    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

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: vba copy sheet limit (Excel2003)

    Hi Rory

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

    zeddy

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba copy sheet limit (Excel2003)

    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.
    Legare Coleman

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: vba copy sheet limit (Excel2003)

    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

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba copy sheet limit (Excel2003)

    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.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba copy sheet limit (Excel2003)

    AFAIK the worksheet codename problem was fixed with Excel XP and with 2000 SR 3.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba copy sheet limit (Excel2003)

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: vba copy sheet limit (Excel2003)

    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
    (Excel Add-ins / Excel Programming)

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

    Re: vba copy sheet limit (Excel2003)

    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.

Posting Permissions

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