Limit to number of sheets and charts in a WB?

[aMember]

I'm running Excel 97 on Windows NT with 512Mb RAM.

My spreadsheet application "empty" contains 76 sheets and 48 charts and tops out at about 3.3Mb.

The user adds products to this WB which causes 2 data sheets, 1 pivot table, 1 chart to be cloned which is where the user's data will be imported to. The 4 sheets cloned are about 1/4mb in size empty.

I can clone 9 sets of sheets without problem.
On the 10th set, it consistently crashes on the same line...

Sheets(Array("999", "999 Pivot&quot).Copy After:=Sheets(Sheets.Count)

This line was executed 9 times before it crashed (Dr. Watson error and everything!).

But....

If I clone 9 sets, save the workbook, exit Excel, re-open the workbook, it lets me add 8 more sets before it crashes agian.

Sounds suspiciously like a memory problem but I cannot figure it out. I do destroy all objects after using them.
Charts do use autosizing but it is necessary and all this is happening even before I import any data.

Is there a limit to the number of sheets/pivot tables/charts you can have in a workbook?

Any response would be great!
amm

 

[xlbo]

I believe it's to do with the "code" name for worksheets. When you add new ones it goes:
Sheet1
Sheet11
Sheet111
Sheet1111
etc etc and there is a limit to the number of characters they can have. At this limit, you'll start getting errors
I also believe that when you save the wb, it resets the "counter" that determines the new sheets codename nad therefore would explain the ability to add more sheets after saving
Try this
For each ws in activeworkbook.worksheets
msgbox ws.codename
next

Unfortunately, the codename appears to be read only but I'm sure I remember a thread on this subject in a different forum - I'll check it out and see if I can find it
You can manually change the codename in the properties window tho.......
Rgds
~Geoff~

 

[xlbo]

okaaaay - now we're getting somewhere. Here's some code (courtesy of Julian Milano - excel L list) which renames the CODENAME of the worksheet (notice that is actually the "_CODENAME" rather than "CODENAME" which is read only

Sub renamer()
i = 1
For Each ws In ActiveWorkbook.Worksheets
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName&quot = "Sheet" & i

i = i + 1
Next
End Sub

Obviously, you can use the
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName&quot = "Sheet" & i
line and hack it to refer to the active worksheet:
activesheet.Parent.VBProject.VBComponents(acticesheet.CodeName).Properties("_CodeName&quot = activeworkbook.sheets.count + 1

or something like that anyway
HTH
Rgds
~Geoff~

 

[aMember]

Thank you Geoff!