Max number of worksheets in a workbook?

[deko]

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/assistance/HP051992911033.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.

 

[keepITcool]

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 :

 

[Andy Wiggins]

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.

 

[deko]

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

 

[deko]

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?

 

[Harald Staff]

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

 

[Chip Pearson]

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

 

[Tom Ogilvy]

You might check out Charles Williams' sight:

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

 

[Dave Peterson]

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

 

[Tom Ogilvy]

sight => site

hopefully he can see fine.

 

[deko]

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

 

[Harald Staff]

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]

Thanks,

I am no longer curious (yellow).

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

 

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

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

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

it,

they

http://office.microsoft.com/en-us/assistance/HP051992911033.aspx?mode=print