Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Max Num of Sheets (any)

    Is there a max number of sheets one can have in a workbook?
    Jackal

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Max Num of Sheets (any)

    From the Excel help file, the maximum number of sheets is limited by available memory.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Max Num of Sheets (any)

    Depends on the version:
    V 2000> Limited to memory;
    V <=97 is 255 sheets!
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Max Num of Sheets (any)

    XL97 is not limited to 255. I just got done verifying this fact by creating a workbook with over 900 sheets in XL97.

    I think this is also limited to memory.
    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Max Num of Sheets (any)

    This is unusual. I distinctly remember reading through the helpfiles in V97 and noticing that it mensioned 255!
    Steve, do me a favour (as I have no access to v97)! If you have access to Office 97, open Excel and type "Limits" into Excel's Help search. In the WorkBook limits they mention the limitations. What do they mention for Number of Sheets!?? (As I say...I remember it saying 255.)
    PS. Did you add those 900 sheets using a macro? That may override the limitation. Try manually. Get 255 sheets in a book and attempt to add a sheet using INSERT | Worksheet! What happens now?
    Regards,
    Rudi

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

    Re: Max Num of Sheets (any)

    Perhaps you were reading about the limit to the maximum number of sheets in a blank new workbook - if you try to set this to a number higher than 255 in the General tab of Tools | Options..., you'll get an error message. This limitation still exists in Excel 2000 and 2002, I don't know about Excel 2003.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Max Num of Sheets (any)

    I could find nothing under "limits" I find many of the limits using "Specifications", but nothing on the number of sheets.

    To get them I manually did it. I select all the sheets and inserted, then selected all the sheets and inserted, doubling each time. It got "slow" inserting 250+ sheets in "one fell swoop", and took a while to add the next 500 and I got bored with it

    I tried going to 254 and inserting several manually afterwards with no problem. I have spreadsheets that I work with (not just test ones) that have over 300 sheets and have had no problems.

    I do get problems in code after about 910. I get runtime error on the add method failing (though it still adds them after acknowledging it), manually gets no error, but takes longer and longer to add another, but I imagine this is memory issue not an excel issue. It will probably be lower limit if the sheets weren't blank.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Max Num of Sheets (any)

    Quite a few of the Excel specifications are "Limited by available memory" but the available memory is not the amount of RAM or size of the swap-file. Excel has its own memory manager and memory limits so you can get the various

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

    Re: Max Num of Sheets (any)

    This is from Excel 97 help:

    Maximum number of sheets in a workbook Limited by available memory (default number of sheets is 3; the maximum number of sheets in a default workbook is 255)

    the number 255 refers to the max default number of sheets in a new workbook as specified in Tools, options.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Max Num of Sheets (any)

    I found it also now <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I was looking under "Worksheet and workbook specifications", but it is in the help under "Workspace specifications"

    Steve

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Max Num of Sheets (any)

    Why would it limit you to 255 in the options dialog, when you can have more anyway???
    Regards,
    Rudi

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Max Num of Sheets (any)

    They probabaly need a max and thought that starting every new workbook with more than 255 sheets would not really be necessary.

    Even when you get to dozens of sheets, moving around and finding the correct sheet is a pain. On some level, with a better design, you don't need a lot of sheets. The one I have that has over 300 sheets, was an "inherited spreadsheet" that we just added to, I never had the time to implement a "redesign", to only use a handful of sheets: there was some outline of the revamp and plans, but no concerted effort.

    And once I moved to another group, it became lower on my priority list, since I don't use it anymore.
    Steve

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

    Re: Max Num of Sheets (any)

    Probably because some programmer used a one byte field to keep the default in.
    Legare Coleman

  14. #14
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Max Num of Sheets (any)

    Thanks to all of you for the info.
    Jackal

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Max Num of Sheets (any)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Thats probably it Legare! Just imagine the issues if he used a Double....
    Regards,
    Rudi

Posting Permissions

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