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.

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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

> 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

> 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?

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" <[email protected]> skrev i melding
news:[email protected]...
> 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.
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

You might check out Charles Williams' sight:

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

--
Regards,
Tom Ogilvy


"deko" <[email protected]> wrote in message
news:[email protected]...
> > 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?
>
>

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" <[email protected]> skrev i melding
> news:[email protected]...
> > 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

sight => site

hopefully he can see fine.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> You might check out Charles Williams' sight:
>
> http://www.decisionmodels.com/memlimits.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
> "deko" <[email protected]> wrote in message
> news:[email protected]...
> > > 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?
> >
> >
>
>

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

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" <[email protected]> skrev i melding
news:[email protected]...
> 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" <[email protected]> skrev i melding
> > news:[email protected]...
> > > 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

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" <[email protected]> skrev i melding
> news:[email protected]...
> > 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" <[email protected]> skrev i melding
> > > news:[email protected]...
> > > > 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

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

--
Regards,
Tom Ogilvy

"Harald Staff" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> skrev i melding
> news:[email protected]...
> > 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" <[email protected]> skrev i melding
> > > news:[email protected]...
> > > > 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
>
>

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