Copy Sheet Error/Limit

I have integrated following code teken from
http://groups.google.co.uk/group/mic...9efbd555e9483a

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/micro...7e7df0dd64bfa1

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

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.

--
Regards,
Tom Ogilvy


"miha" wrote:

> I have integrated following code teken from
> http://groups.google.co.uk/group/mic...9efbd555e9483a
>
> 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/micro...7e7df0dd64bfa1
>
> Does anyone has a sollution as I have to copy Master sheet 500 times.
>
>

I have Excel 2003.

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

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.

--
Regards,
Tom Ogilvy



"miha" wrote:

> I have Excel 2003.
>
> I need 500 sheets or more as I use this for bills in company.
>
>

I have found the solution at
http://support.microsoft.com/default...b;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.

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?

"miha" wrote:

> I have found the solution at
> http://support.microsoft.com/default...b;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.
>
>