What's the magic number?

[thebeachboyz]

How many excel worksheets are allowed in ONE excel workbook?

 

[Todd Bardoni]

Its unlimited.

 

[thebeachboyz]

so I can keep on populating worksheets but it'll just chew up my memory?

 

[Tazguy37]

The help files say that the number is limited only by available memory. I haven't actually tested this theory

 

[Todd Bardoni]

so I can keep on populating worksheets but it'll just chew up my memory?

Yep. As a test I just added 6,500 wks to a workbook. It crashed my Excel...but I was able to do it.

 

[Ekim]

To see all Excel specifications and limitations:

Go to the Excel Help menu.
Type “Excel Specifications” (no quotes) in the Search box.
Click the Search button.

 

[Nimrod]

thebeachboyz

How many are you wanting to create ??

 

[thebeachboyz]

I think at the most it's going to create close to 500 sheets. BTW, my system is a P4 3.0 GHz 512MB. Thanks

 

[Nimrod]

If these are sheets with formulas you will probably find that your having major problems way before 500 sheets .
I've had wookbooks with as little as 10 sheets have problems, when there were alot of formula. (E.g. Take minutes to open or to recalculate ). You may find that Access or SQL may be the solution that is required. Excel doesn't aways allow you to scale the solution to the size required. In other words the solution works when you start out but as the amount of data increases the sheets begin to crash . Now you've implemented a solution that needs to be scrapped. Excel was never designed with this level of usage in mind.

If you don't mind me asking what are you doing that requires so many sheets ??

TO HELP YOU EXPERIMENT.... I've created 3 Procedures that will allow you to experment with number of sheets and the amount of Data and Formulas in each ( I just created 1 sheet with 60000 formula and 1 sheet of 60000 data and had the system crash ... and I have a gig of memory )

Public Sub AddSheets()
NumOfSheets = Application.InputBox("How many sheets to add to workbook?", "Add Sheets", Type:=1)
If NumOfSheets = False Or NumOfSheets = "" Then Exit Sub
TotalSheets = NumOfSheets + Sheets.Count
Do
Sheets.Add
Loop Until Sheets.Count = TotalSheets
End Sub

Public Sub AddFormula()
RowsOfFormula = Application.InputBox("How many Rows of Formula in each Sheet", "Add Formula", Type:=1)
If RowsOfFormula = False Or RowsOfFormula = "" Then Exit Sub

For Each Sh In Worksheets
If RowOfFormula > 60000 Then RowsOfFormula = 60000
With Sheets(Sh.Name)
.Range("A2:IV" & RowsOfFormula).Formula = "=" & .Name & "!$A$1 + 1000 /5 * 2.4"
End With
Next Sh
End Sub

Public Sub AddData()
RowsOfData = Application.InputBox("How many Rows of data in each Sheet", "Add data", Type:=1)
If RowsOfData = False Or RowsOfData = "" Then Exit Sub

For Each Sh In Worksheets
If RowOfFormula > 60000 Then RowsOfData = 60000
With Sheets(Sh.Name)
.Range("A2:IV" & RowsOfData).Value = Val(Format(Time(), "ssss")) * 10000
End With
Next Sh
End Sub

 

[Nimrod]

In other words there is no magic number , it all depends on such things as :
Number of worksheets open
Amount of ram
number of formula in worksheets
Are formulas volitile
How complicated are the formula
Are there links to other work books
How much data in each worksheet
How long since you last rebooted your pc
How much data you have in clipboard
How much copiing between workbooks and sheets have you just done ( MS has documented memory leak from copiing sheets )
etc etc etc

However the procedures I put in my last posting will help you somewhat understand how much you should put in any one workbook. Though I generally limit myself to 12 sheets with each sheet %60 full of data or %25 full of formula.