Max sheets

[par60056]

Anybody know what the maximum number of sheets Excel can create is?

I have a process that creates a huge number of sheets formatting them and putting them in workbooks for distribution.

I can run it twice without shutting Excel down. If I run it a 3rd time, I eventually get an error message on a call to add a worksheet. Once this happens, if I try to delete a sheet, move a sheet or add a sheet, Excel crashes. It would be nice to know where the maximum is so that I can through a stop in the process before crashing hard.

 

[number kruncher]

It depends on your version of Excel.

2007 and before will be limited to 256 sheets, however for 2010, see Excel specifications and limits - Excel - Office.com

HTH

 

[par60056]

I am using 2010.

That is the number of sheets in a single book.

I have a master book that adds sheets and then moves them to another book. No single book has more than 10 sheets in it. It is generally either 7 or 3 sheets in a book. When the process finishes there are 283 books.

Each time Sheets.Add is called, a new numbered sheet is obtained. Somewhere between sheet10903 and sheet11150, Sheet.Add fails (the sheet is actually created in the master book but an error is returned) and when I do anything to add, delete or move a sheet, Excel crashes.

 

[number kruncher]

Without knowing more details, I would say it is a toss up between your Software Limits or your Hardware Limits.

Maybe you could batch process, say to 250 workbooks at a time?

 

[par60056]

Yes it is definitely a limit in the software. Sheets.Add should NEVER cause Excel to crash. The entire run never exceed 1/2 of system memory. I can make 2 complete runs but then I have to exit Excel before the 3rd run.

 

[Jonmo1]

I see this issue pop up every now and then, and I believe a solution (or workaround) is to periodically save the master book between creating sheets.

If you're creating about 10 sheets per book, then save the MASTER book between each "other book" that the sheets are moved to.

Also, instead of creating the sheets in the master book, then MOVING them to another book..
Why not create the sheets in "the other book" in the first place??

I am using 2010.

That is the number of sheets in a single book.

I have a master book that adds sheets and then moves them to another book. No single book has more than 10 sheets in it. It is generally either 7 or 3 sheets in a book. When the process finishes there are 283 books.

Each time Sheets.Add is called, a new numbered sheet is obtained. Somewhere between sheet10903 and sheet11150, Sheet.Add fails (the sheet is actually created in the master book but an error is returned) and when I do anything to add, delete or move a sheet, Excel crashes.