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?

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?

This worked for me way beyond 46 (XL 2002)

Sub test()
Do
ThisWorkbook.Sheets(1).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
Loop
End Sub

I seem to remember there was an issue in earlier version which was
connection to the worksheet *codenames* (look in the VBE at the names of the
copied sheets). Instead of naming then incrementally, Excel would just add
the string "1" to the end of the name until it reached the length limit.
So you could try fixing the codename after copying the sheet.

Tim.



"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?

Thanks, your right. I used this on a new blank workbook and can add as many
sheets as I like. Unfortunately when I do it in the workbook I was originally
working on it still fails at 46 sheets with "Runtime error '1004', Copy
method of worksheet class failed"

I'll have to check the rest of the code to see if there is some other
problem causing this. The sheet I want to make multiple copies of has some
hyperlinks, various formatting, is protected etc. The macro also reads
another sheet to identify a list of how many sheets to be added and their
names. Perhaps the problem lies there.

If I get a solution or closer to it I will update this post.

"Tim Williams" wrote:

This worked for me way beyond 46 (XL 2002)

Sub test()
Do
ThisWorkbook.Sheets(1).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
Loop
End Sub

I seem to remember there was an issue in earlier version which was
connection to the worksheet *codenames* (look in the VBE at the names of the
copied sheets). Instead of naming then incrementally, Excel would just add
the string "1" to the end of the name until it reached the length limit.
So you could try fixing the codename after copying the sheet.

Tim.



"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?

I have tried various methods:

1.Copy sheet from within workbook
2.Add new sheet and just copy the cells from master sheet
3.Add new sheet from template locaton rather than within workbook

and come to the conclusion that something in the spreadsheet itself may be
causing the problem.

I can add as many sheets as I like from VBA on any workbook except the one I
want to !

I have also discovered that when the error occurs any subsequent attempt to
run the macro fails and sheets cannot be copied, even when done manually. If
all the copied sheets are deleted to put the workbook back to its original
state and then it is saved the macro does not work at all when you re-open
the workbook. Neither can sheets be copied manually.

This suggests that Excel is storing some kind of marker wrt the number of
copied sheets which is not cleared simply by deleting them. I don't think it
is memory limit as I have successfully copied up to 200 sheets and consequent
file size of 3MB on another workbook.

If anyone knows how to clear this it would eliminate the problem.

In the mean time it looks like I might need to start from scratch to
recreate the worksheet I need to copy.