Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet Error/Limit
I have Excel 2003.
I need 500 sheets or more as I use this for bills in company. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy sheet to different workbook sizing error message | Excel Discussion (Misc queries) | |||
Sheet copy limit | Excel Discussion (Misc queries) | |||
Move/Copy sheet to another workbook error | Excel Discussion (Misc queries) | |||
copy sheet error message | New Users to Excel | |||
Copy Sheet Limit | Excel Programming |