Make a workbook with 10 copies of your template and copy them 10 at a time.

--
Regards,
Tom Ogilvy

"pumpbhoy" wrote in message
...
I have an excel workbook (Excel 2000) which contains a macro to create new
sheets. For some reason the macro works up to 46 sheets and then fails.

The workbook in question contains 3 sheets. One of these is a blank
template
which is copied to create any additional sheets required.

I have subsequently found a workround by adding a new sheet rather than
copying and then copying and pasting the required cells from the blank
template sheet. This will allow me to have as many sheets as necessary but
is
much slower than just copying the entire sheet.

Someone has already suggested following:

"I don't believe it has to do with the limitation in the accual sheets of
excel. I believe it to be the way in which VB creates new sheets. For each
sheet it creates, it creates a pointer, the next sheet is a pointer to the
first sheet, that has a pointer to the origial sheet, as each sheet is
created in VB a new pointer is created to the last sheet.

If however you do it manualy, it creates a pointer to the base sheet so
you
do not get a string of pointers, which do not result in a overflow."

Does anyone have any suggested fixes for this?