Copy Sheet Error/Limit

[miha]

I have integrated following code teken from
http://groups.google.co.uk/group/mi...read/thread/fb1f029e87a0d5a8/6f9efbd555e9483a

Sub Test()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 100
Sheets("Master").Copy After:=Sheets(Sheets.Count)
Sheets("Master (2)").Name = Format(i, "0000")
Next i
Application.ScreenUpdating = True
End Sub

But when coopying Master sheet I got error at 55th copy. "Runtime error
'1004', Copy method of worksheet class failed"
Problem is allready described here
http://groups.google.com/group/micr...3e26f88a768/537e7df0dd64bfa1#537e7df0dd64bfa1

Does anyone has a sollution as I have to copy Master sheet 500 times.

 

[Tom Ogilvy]

What version of Excel. If not xl97, then the link you cited contains several
workarounds.

It isn't clear why you would need 500 worksheets in a workbook, but you
might rethink your design.

 

[miha]

I have Excel 2003.

I need 500 sheets or more as I use this for bills in company.

 

[Tom Ogilvy]

If you are printing the bills, you could make a single form and have a
database. Put data in the form, print, clear the form, put data in the
form, print, clear the form, etc. (obviously doing this with code, not by
hand).

If you need a file with the form filed, then after printing you could copy
the sheet to a new workbook and save it. Save all the files in a separate
folder.

that is what I mean by alter your design.
the difference is this might work while right now you don't have a solution.

 

[miha]

I have found the solution at
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684

quote:
| WORKAROUND
| To work around this problem, insert a new worksheet from a
template instead of copying an existing worksheet. To do this:
| 1. Create a new workbook, and then delete all of the worksheets
except for one.
| 2. Format the workbook and add any text, data, and charts that
you must have in the template by default.
| 3. Click File, and then click Save As.
| 4. In the File name box, type the name that you want for the
Excel template.
| 5. In the Save as type list, click Template (*.xlt), and then
click Save.
| 6. To insert the template programmatically, use the following
code:
| Sheets.Add Type:=path\filename
| where path\filename is a string that contains the full path and
file name for your sheet template.

 

[Doug]

I was having the same problem with copy, so tried the workaround below, but
now am getting the error

Run-time error '1004'
Method 'Add' of object 'Sheets' failed

This is very similar to the copy error I was getting. Has anyone had this
problem?