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.

Copy Sheet Error/Limit
 

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.

Copy Sheet Error/Limit
 

I have Excel 2003.

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

Copy Sheet Error/Limit
 

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.

Copy Sheet Error/Limit
 

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.

Copy Sheet Error/Limit
 

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.