Max no. of worksheets

[gibbo1715]

can anyone tell me if there is a maximum number of worksheets per workbook please??

[Steiner]

I read somewhere that this is limited to 256, but I could create more with Excel97.

Another source said that this is only limited by the memory Excel has available and that someone tried it up to 20.000.

Just create a macro for adding worksheets and see how far you can go:


Sub test()
Dim i%
For i = 1 To 250
Worksheets.Add
Next i
End Sub

[patrickab]

Specification for Excel 2002 states:

Sheets in a workbookLimited by available memory (default is 3 sheets)

If you have another version of Excel, press F1 for help and type in 'Excel Specifications' and follow the link - it should have the info' for your edition of Excel.

[mvidas]

I believe it is limited to the memory as well, though I'm not quite sure how. I got 861 by doing:


Sub SheetLimit()
Workbooks.Add
On Error Resume Next
Do Until Err.Number <> 0
Sheets.Add
Loop
On Error GoTo 0
MsgBox Sheets.Count
End Sub

But when I had the task manager open at the same time, the memory didn't go up more than 7mb.
Matt

[MWE]

I too have heard/read that the # of sheets is limited by available memory. But my experience is that Excel does funny things when the number of anything gets large. Excel does not manage the upper end of available memory very well and does not seem to release memory no longer needed. Or perhaps it is similar to other MS appls, i.e., they just keep a lot of deleted junk around until something triggers the spring cleaning.

After having problems with # of charts allowed, I ran some tests on several different machines (all running Win2K and Excel2000 but with different amounts of memory - 256MB, 512MB and 1024MB). There were some differences in the number/complexity of charts, but all tests acted strangely as the # of charts exceeded 256

[Paleo]

Here you will find many explanations on the way Excel (amont other applications) behave on memory issues.

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

[Jacob Hilderbrand]

You are limited to 256 sheets that can be created with a new workbook. But after the workbook is created you can add as many sheets as you want until you run out of memory.

[gibbo1715]

thankyou all

[Paleo]

You are welcome!