+ Reply to Thread
Results 1 to 15 of 15

Max number of worksheets in a workbook?

  1. #1
    deko
    Guest

    Max number of worksheets in a workbook?

    I've heard that the maximum number of worksheets allowed in an Excel
    workbook is 255. Is this urban myth? I just had a look at "Excel
    specifications and limits" for XL2003 at
    http://office.microsoft.com/en-us/as...spx?mode=print
    and the Maximum limit is defined as "Limited by available memory (default is
    3 sheets)". Is this true only for XL2003? What about XL2000?

    The only thing I've read about chart limitations is in regard to "Worksheets
    referred to by a chart" - which is no an issue for me.

    So it's possible to have 500 worksheets and 2000 charts? The only
    limitation is memory/processor?

    Thanks in advance.



  2. #2
    keepITcool
    Guest

    Re: Max number of worksheets in a workbook?

    just create a workbook with 100 sheets

    select all sheets

    type1 in a1
    type1 in e10000

    select sheet1

    save

    look at size.

    nuf said.


    --
    keepITcool
    | www.XLsupport.com | keepITcool chello nl | amsterdam


    deko wrote :

    > I've heard that the maximum number of worksheets allowed in an Excel
    > workbook is 255. Is this urban myth? I just had a look at "Excel
    > specifications and limits" for XL2003 at
    > http://office.microsoft.com/en-us/as...033.aspx?mode=
    > print and the Maximum limit is defined as "Limited by available
    > memory (default is 3 sheets)". Is this true only for XL2003? What
    > about XL2000?
    >
    > The only thing I've read about chart limitations is in regard to
    > "Worksheets referred to by a chart" - which is no an issue for me.
    >
    > So it's possible to have 500 worksheets and 2000 charts? The only
    > limitation is memory/processor?
    >
    > Thanks in advance.


  3. #3
    Andy Wiggins
    Guest

    Re: Max number of worksheets in a workbook?

    The number of sheets in a new workbook is 255 (You set this here: Tools >
    Options: General, "Sheets in new workbook") , but you can then add more
    sheets until your system's resources run out. This has been the case since
    Excel 95.

    --
    Andy Wiggins FCCA
    www.BygSoftware.com
    Excel, Access and VBA Consultancy
    -

    "deko" <deko@deko.com> wrote in message
    news:mCPte.1974$Bx6.956@newssvr13.news.prodigy.com...
    > I've heard that the maximum number of worksheets allowed in an Excel
    > workbook is 255. Is this urban myth? I just had a look at "Excel
    > specifications and limits" for XL2003 at
    >

    http://office.microsoft.com/en-us/as...spx?mode=print
    > and the Maximum limit is defined as "Limited by available memory (default

    is
    > 3 sheets)". Is this true only for XL2003? What about XL2000?
    >
    > The only thing I've read about chart limitations is in regard to

    "Worksheets
    > referred to by a chart" - which is no an issue for me.
    >
    > So it's possible to have 500 worksheets and 2000 charts? The only
    > limitation is memory/processor?
    >
    > Thanks in advance.
    >
    >




  4. #4
    deko
    Guest

    Re: Max number of worksheets in a workbook?

    > The number of sheets in a new workbook is 255 (You set this here: Tools >
    > Options: General, "Sheets in new workbook") , but you can then add more
    > sheets until your system's resources run out. This has been the case since
    > Excel 95.


    Silly me. I export to Excel from Access and had written code to handle
    this...

    If z > 254 Then Err.Raise WORKBOOK_FULL



  5. #5
    deko
    Guest

    Re: Max number of worksheets in a workbook?

    > select all sheets
    >
    > type1 in a1
    > type1 in e10000
    >
    > select sheet1
    >
    > save
    >
    > look at size.


    For some reason that didn't work for me.

    But I tried this:

    right click on worksheet tab
    select all sheets
    right click on any worksheet tab and select insert
    click OK on Insert dialog
    open immediate window
    debug.Print worksheets.Count
    repeat

    Results:

    Original number of sheets: 60
    Original size: 8.12Mg

    debug.Print worksheets.Count
    120
    debug.Print worksheets.Count
    240
    debug.Print worksheets.Count
    480
    debug.Print worksheets.Count
    960

    At 960 the size was 9.51Mg - after inserting 900 empty worksheets.
    Performance was acceptable, but somewhat slow opening and saving.

    If I inserted copies of the existing worksheets, my guess is the size would
    be just over 100Mb. What I'd like to do is find a way to write a loop that
    would copy/rename/insert a particular worksheet in the workbook, and test
    again at the above intervals. That way I'd know what the practical ceiling
    is for my app.

    Any suggestions on how to construct such a loop?



  6. #6
    Harald Staff
    Guest

    Re: Max number of worksheets in a workbook?

    Hi

    This spring I was handed a workbook with 1600 worksheets in it, each sheet
    containing a chart. I volunteered to reorganize that work, believing that
    the thing would self destruct at a point very soon. While I rewrote it, they
    continued to use it.

    It died while adding sheet number 2005 into it. So the limit is either 2004,
    or maybe "last year". Depends on the content of course. But the point is,
    that file will not be fine again just with more RAM, it killed itself
    because of its size. So don't push those limits.

    HTH. Best wishes Harald

    "deko" <deko@deko.com> skrev i melding
    news:mCPte.1974$Bx6.956@newssvr13.news.prodigy.com...
    > I've heard that the maximum number of worksheets allowed in an Excel
    > workbook is 255. Is this urban myth? I just had a look at "Excel
    > specifications and limits" for XL2003 at
    >

    http://office.microsoft.com/en-us/as...spx?mode=print
    > and the Maximum limit is defined as "Limited by available memory (default

    is
    > 3 sheets)". Is this true only for XL2003? What about XL2000?
    >
    > The only thing I've read about chart limitations is in regard to

    "Worksheets
    > referred to by a chart" - which is no an issue for me.
    >
    > So it's possible to have 500 worksheets and 2000 charts? The only
    > limitation is memory/processor?
    >
    > Thanks in advance.
    >
    >




  7. #7
    Chip Pearson
    Guest

    Re: Max number of worksheets in a workbook?

    The 255 sheet limit is the maximum number of sheets that sheets
    allowed in a newly created workbook. It is the upper limit
    imposed by the Application.SheetsInNewWorkbook property.

    You can add aditional sheets as required by your application.
    There is no hard limit to the number of sheets in a workbook. It
    is limited by the memory available to Excel.


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com



    "deko" <deko@deko.com> wrote in message
    news:mCPte.1974$Bx6.956@newssvr13.news.prodigy.com...
    > I've heard that the maximum number of worksheets allowed in an
    > Excel
    > workbook is 255. Is this urban myth? I just had a look at
    > "Excel
    > specifications and limits" for XL2003 at
    > http://office.microsoft.com/en-us/as...spx?mode=print
    > and the Maximum limit is defined as "Limited by available
    > memory (default is
    > 3 sheets)". Is this true only for XL2003? What about XL2000?
    >
    > The only thing I've read about chart limitations is in regard
    > to "Worksheets
    > referred to by a chart" - which is no an issue for me.
    >
    > So it's possible to have 500 worksheets and 2000 charts? The
    > only
    > limitation is memory/processor?
    >
    > Thanks in advance.
    >
    >




  8. #8
    Tom Ogilvy
    Guest

    Re: Max number of worksheets in a workbook?

    You might check out Charles Williams' sight:

    http://www.decisionmodels.com/memlimits.htm

    --
    Regards,
    Tom Ogilvy


    "deko" <deko@deko.com> wrote in message
    news:RGQte.1982$Bx6.1636@newssvr13.news.prodigy.com...
    > > select all sheets
    > >
    > > type1 in a1
    > > type1 in e10000
    > >
    > > select sheet1
    > >
    > > save
    > >
    > > look at size.

    >
    > For some reason that didn't work for me.
    >
    > But I tried this:
    >
    > right click on worksheet tab
    > select all sheets
    > right click on any worksheet tab and select insert
    > click OK on Insert dialog
    > open immediate window
    > debug.Print worksheets.Count
    > repeat
    >
    > Results:
    >
    > Original number of sheets: 60
    > Original size: 8.12Mg
    >
    > debug.Print worksheets.Count
    > 120
    > debug.Print worksheets.Count
    > 240
    > debug.Print worksheets.Count
    > 480
    > debug.Print worksheets.Count
    > 960
    >
    > At 960 the size was 9.51Mg - after inserting 900 empty worksheets.
    > Performance was acceptable, but somewhat slow opening and saving.
    >
    > If I inserted copies of the existing worksheets, my guess is the size

    would
    > be just over 100Mb. What I'd like to do is find a way to write a loop

    that
    > would copy/rename/insert a particular worksheet in the workbook, and test
    > again at the above intervals. That way I'd know what the practical

    ceiling
    > is for my app.
    >
    > Any suggestions on how to construct such a loop?
    >
    >




  9. #9
    Dave Peterson
    Guest

    Re: Max number of worksheets in a workbook?

    And when you finished your rewrite, you had how many sheets?

    Did you create the charts on the fly or some other approach?

    Signed,

    Just Curious

    Harald Staff wrote:
    >
    > Hi
    >
    > This spring I was handed a workbook with 1600 worksheets in it, each sheet
    > containing a chart. I volunteered to reorganize that work, believing that
    > the thing would self destruct at a point very soon. While I rewrote it, they
    > continued to use it.
    >
    > It died while adding sheet number 2005 into it. So the limit is either 2004,
    > or maybe "last year". Depends on the content of course. But the point is,
    > that file will not be fine again just with more RAM, it killed itself
    > because of its size. So don't push those limits.
    >
    > HTH. Best wishes Harald
    >
    > "deko" <deko@deko.com> skrev i melding
    > news:mCPte.1974$Bx6.956@newssvr13.news.prodigy.com...
    > > I've heard that the maximum number of worksheets allowed in an Excel
    > > workbook is 255. Is this urban myth? I just had a look at "Excel
    > > specifications and limits" for XL2003 at
    > >

    > http://office.microsoft.com/en-us/as...spx?mode=print
    > > and the Maximum limit is defined as "Limited by available memory (default

    > is
    > > 3 sheets)". Is this true only for XL2003? What about XL2000?
    > >
    > > The only thing I've read about chart limitations is in regard to

    > "Worksheets
    > > referred to by a chart" - which is no an issue for me.
    > >
    > > So it's possible to have 500 worksheets and 2000 charts? The only
    > > limitation is memory/processor?
    > >
    > > Thanks in advance.
    > >
    > >


    --

    Dave Peterson

  10. #10
    Tom Ogilvy
    Guest

    Re: Max number of worksheets in a workbook?

    sight => site

    hopefully he can see fine.

    --
    Regards,
    Tom Ogilvy


    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:%23bsyWPndFHA.3880@tk2msftngp13.phx.gbl...
    > You might check out Charles Williams' sight:
    >
    > http://www.decisionmodels.com/memlimits.htm
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "deko" <deko@deko.com> wrote in message
    > news:RGQte.1982$Bx6.1636@newssvr13.news.prodigy.com...
    > > > select all sheets
    > > >
    > > > type1 in a1
    > > > type1 in e10000
    > > >
    > > > select sheet1
    > > >
    > > > save
    > > >
    > > > look at size.

    > >
    > > For some reason that didn't work for me.
    > >
    > > But I tried this:
    > >
    > > right click on worksheet tab
    > > select all sheets
    > > right click on any worksheet tab and select insert
    > > click OK on Insert dialog
    > > open immediate window
    > > debug.Print worksheets.Count
    > > repeat
    > >
    > > Results:
    > >
    > > Original number of sheets: 60
    > > Original size: 8.12Mg
    > >
    > > debug.Print worksheets.Count
    > > 120
    > > debug.Print worksheets.Count
    > > 240
    > > debug.Print worksheets.Count
    > > 480
    > > debug.Print worksheets.Count
    > > 960
    > >
    > > At 960 the size was 9.51Mg - after inserting 900 empty worksheets.
    > > Performance was acceptable, but somewhat slow opening and saving.
    > >
    > > If I inserted copies of the existing worksheets, my guess is the size

    > would
    > > be just over 100Mb. What I'd like to do is find a way to write a loop

    > that
    > > would copy/rename/insert a particular worksheet in the workbook, and

    test
    > > again at the above intervals. That way I'd know what the practical

    > ceiling
    > > is for my app.
    > >
    > > Any suggestions on how to construct such a loop?
    > >
    > >

    >
    >




  11. #11
    deko
    Guest

    Re: Max number of worksheets in a workbook?

    > > http://www.decisionmodels.com/memlimits.htm

    Interesting stats. Bottom line: the bigger the workbook, the more RAM you
    need.

    what's happening in my case is an export from Access creating any number of
    worksheets - depending on how many data files the user points the app at.
    The app can run for 30 or so minutes on slower machines when creating 120
    worksheets. Each worksheet has at least one chart (and some have up to 10
    charts) so I'm worried about users creating workbooks so big they can't open
    them - and blaming my app. Perhaps I should impose a 255 worksheet limit...



  12. #12
    Harald Staff
    Guest

    Re: Max number of worksheets in a workbook?

    Hi Julius Curius

    One sheet an one chart for processing, one userform for controlling the
    thing and an Access mdb file containing all data. The best of three worlds
    <g>

    Best wishes Harald

    "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i melding
    news:42B855CC.936074A5@netscapeXSPAM.com...
    > And when you finished your rewrite, you had how many sheets?
    >
    > Did you create the charts on the fly or some other approach?
    >
    > Signed,
    >
    > Just Curious
    >
    > Harald Staff wrote:
    > >
    > > Hi
    > >
    > > This spring I was handed a workbook with 1600 worksheets in it, each

    sheet
    > > containing a chart. I volunteered to reorganize that work, believing

    that
    > > the thing would self destruct at a point very soon. While I rewrote it,

    they
    > > continued to use it.
    > >
    > > It died while adding sheet number 2005 into it. So the limit is either

    2004,
    > > or maybe "last year". Depends on the content of course. But the point

    is,
    > > that file will not be fine again just with more RAM, it killed itself
    > > because of its size. So don't push those limits.
    > >
    > > HTH. Best wishes Harald
    > >
    > > "deko" <deko@deko.com> skrev i melding
    > > news:mCPte.1974$Bx6.956@newssvr13.news.prodigy.com...
    > > > I've heard that the maximum number of worksheets allowed in an Excel
    > > > workbook is 255. Is this urban myth? I just had a look at "Excel
    > > > specifications and limits" for XL2003 at
    > > >

    > >

    http://office.microsoft.com/en-us/as...spx?mode=print
    > > > and the Maximum limit is defined as "Limited by available memory

    (default
    > > is
    > > > 3 sheets)". Is this true only for XL2003? What about XL2000?
    > > >
    > > > The only thing I've read about chart limitations is in regard to

    > > "Worksheets
    > > > referred to by a chart" - which is no an issue for me.
    > > >
    > > > So it's possible to have 500 worksheets and 2000 charts? The only
    > > > limitation is memory/processor?
    > > >
    > > > Thanks in advance.
    > > >
    > > >

    >
    > --
    >
    > Dave Peterson




  13. #13
    Dave Peterson
    Guest

    Re: Max number of worksheets in a workbook?

    Thanks,

    I am no longer curious (yellow).



    Harald Staff wrote:
    >
    > Hi Julius Curius
    >
    > One sheet an one chart for processing, one userform for controlling the
    > thing and an Access mdb file containing all data. The best of three worlds
    > <g>
    >
    > Best wishes Harald
    >
    > "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i melding
    > news:42B855CC.936074A5@netscapeXSPAM.com...
    > > And when you finished your rewrite, you had how many sheets?
    > >
    > > Did you create the charts on the fly or some other approach?
    > >
    > > Signed,
    > >
    > > Just Curious
    > >
    > > Harald Staff wrote:
    > > >
    > > > Hi
    > > >
    > > > This spring I was handed a workbook with 1600 worksheets in it, each

    > sheet
    > > > containing a chart. I volunteered to reorganize that work, believing

    > that
    > > > the thing would self destruct at a point very soon. While I rewrote it,

    > they
    > > > continued to use it.
    > > >
    > > > It died while adding sheet number 2005 into it. So the limit is either

    > 2004,
    > > > or maybe "last year". Depends on the content of course. But the point

    > is,
    > > > that file will not be fine again just with more RAM, it killed itself
    > > > because of its size. So don't push those limits.
    > > >
    > > > HTH. Best wishes Harald
    > > >
    > > > "deko" <deko@deko.com> skrev i melding
    > > > news:mCPte.1974$Bx6.956@newssvr13.news.prodigy.com...
    > > > > I've heard that the maximum number of worksheets allowed in an Excel
    > > > > workbook is 255. Is this urban myth? I just had a look at "Excel
    > > > > specifications and limits" for XL2003 at
    > > > >
    > > >

    > http://office.microsoft.com/en-us/as...spx?mode=print
    > > > > and the Maximum limit is defined as "Limited by available memory

    > (default
    > > > is
    > > > > 3 sheets)". Is this true only for XL2003? What about XL2000?
    > > > >
    > > > > The only thing I've read about chart limitations is in regard to
    > > > "Worksheets
    > > > > referred to by a chart" - which is no an issue for me.
    > > > >
    > > > > So it's possible to have 500 worksheets and 2000 charts? The only
    > > > > limitation is memory/processor?
    > > > >
    > > > > Thanks in advance.
    > > > >
    > > > >

    > >
    > > --
    > >
    > > Dave Peterson


    --

    Dave Peterson

  14. #14
    Tom Ogilvy
    Guest

    Re: Max number of worksheets in a workbook?

    Without knowing more, that and perhaps a pivottable might be one solution
    for Deko.

    --
    Regards,
    Tom Ogilvy

    "Harald Staff" <innocent@enron.invalid> wrote in message
    news:%2351kIvvdFHA.1504@TK2MSFTNGP15.phx.gbl...
    > Hi Julius Curius
    >
    > One sheet an one chart for processing, one userform for controlling the
    > thing and an Access mdb file containing all data. The best of three worlds
    > <g>
    >
    > Best wishes Harald
    >
    > "Dave Peterson" <ec35720@netscapeXSPAM.com> skrev i melding
    > news:42B855CC.936074A5@netscapeXSPAM.com...
    > > And when you finished your rewrite, you had how many sheets?
    > >
    > > Did you create the charts on the fly or some other approach?
    > >
    > > Signed,
    > >
    > > Just Curious
    > >
    > > Harald Staff wrote:
    > > >
    > > > Hi
    > > >
    > > > This spring I was handed a workbook with 1600 worksheets in it, each

    > sheet
    > > > containing a chart. I volunteered to reorganize that work, believing

    > that
    > > > the thing would self destruct at a point very soon. While I rewrote

    it,
    > they
    > > > continued to use it.
    > > >
    > > > It died while adding sheet number 2005 into it. So the limit is either

    > 2004,
    > > > or maybe "last year". Depends on the content of course. But the point

    > is,
    > > > that file will not be fine again just with more RAM, it killed itself
    > > > because of its size. So don't push those limits.
    > > >
    > > > HTH. Best wishes Harald
    > > >
    > > > "deko" <deko@deko.com> skrev i melding
    > > > news:mCPte.1974$Bx6.956@newssvr13.news.prodigy.com...
    > > > > I've heard that the maximum number of worksheets allowed in an Excel
    > > > > workbook is 255. Is this urban myth? I just had a look at "Excel
    > > > > specifications and limits" for XL2003 at
    > > > >
    > > >

    >

    http://office.microsoft.com/en-us/as...spx?mode=print
    > > > > and the Maximum limit is defined as "Limited by available memory

    > (default
    > > > is
    > > > > 3 sheets)". Is this true only for XL2003? What about XL2000?
    > > > >
    > > > > The only thing I've read about chart limitations is in regard to
    > > > "Worksheets
    > > > > referred to by a chart" - which is no an issue for me.
    > > > >
    > > > > So it's possible to have 500 worksheets and 2000 charts? The only
    > > > > limitation is memory/processor?
    > > > >
    > > > > Thanks in advance.
    > > > >
    > > > >

    > >
    > > --
    > >
    > > Dave Peterson

    >
    >




  15. #15
    Registered User
    Join Date
    04-05-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    1

    Re: Max number of worksheets in a workbook?

    Nothing like replying to a thread which is 10+ years old, but my contention is that the answer(s) here are inaccurate.

    ** PLEASE NOTE: I'm am specifically *not* recommending creating a spreadsheet with thousands of sheets;
    if someone has the potential to do this there are likely to be many better ways of proceeding. **

    I've been developing an addin which I've stress tested: how many sheets can the addin's analysis and functionality cope with?
    I created an Excel spreadheet with over 31,000 sheets - but which is nonetheless only c25MB in size.
    I successfully manipulated, saved and (just about!) re-opened the spreadsheet.

    Despite this there is no doubt in my mind that Excel struggles with its internal list of worksheets. And of course
    human beings struggle somewhat more. What we need is an addin giving a robust navigation system

    Andrew

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1