Runtime Error '1004': Copy of method Worksheet Class Failed  
Author Message
Ranya





PostPosted: Visual Basic for Applications (VBA), Runtime Error '1004': Copy of method Worksheet Class Failed Top

I have a function designed to create a new worksheet by copying and pasting a template. It works fine for the first many sheets, but beyond a certain number of worksheets, it fails. I am confident that I have ample memory to continue creating new worksheets, however, at a certain point i get Runtime Error '1004': Copy of method Worksheet Class Failed.

Here is the function I am calling:

Function MakeNewSheet() As String
Dim shtcount As Integer

shtcount = ThisWorkbook.Sheets.count
ThisWorkbook.Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(shtcount)

End Function

The de**** identifies the text in bold as the location of the problem where shtcount = 42.

Solutions



Microsoft ISV Community Center Forums3  
 
 
Navajo





PostPosted: Visual Basic for Applications (VBA), Runtime Error '1004': Copy of method Worksheet Class Failed Top

See MS Support article 210684:

Copying worksheet programmatically causes run-time error 1004 in Excel



 
 
patl





PostPosted: Visual Basic for Applications (VBA), Runtime Error '1004': Copy of method Worksheet Class Failed Top

The suggested workaround (saving, closing and reopening the workbook) fails if the initial workbook was saved over a pre-existing workbook. Even if it was saved over the pre-existing workbook before anything was copied to it. ie- when you first create the blank workbook.

Can anyone suggest a reasonable workaround. Problem is that i allow the user to select where they want to save the report before it is generated. They inevable save it over another excel file, and when this is done, I get the copy error eventhough i make the code routinely save, close, and reopen the workbook.

Also, what version of excel or service pack will fix the initial problem Microsoft just says they acknowledge it as a problem but dont say anything about when/how it will be fixed. Anyway, thanks a lot for any help you can give me.


 
 
Jon Peltier





PostPosted: Visual Basic for Applications (VBA), Runtime Error '1004': Copy of method Worksheet Class Failed Top

This is frustrating. I have tried various workarounds, with limited success.

You might have better luck if you save that Templates sheet in its own workbook someplace, and insert the sheet using:

Sheets.Add Type:="C:\test\TestBook.xls", After:=Worksheets(Worksheets.Count)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



 
 
bshammer





PostPosted: Visual Basic for Applications (VBA), Runtime Error '1004': Copy of method Worksheet Class Failed Top

Thank you for that suggestion. This copies all of the worksheets in the workbook. Is there a way to limit it to one worksheet (if I specify which of the many sheets in the file I want copied)

Thanks


 
 
Jon Peltier





PostPosted: Visual Basic for Applications (VBA), Runtime Error '1004': Copy of method Worksheet Class Failed Top

Well, you could try inserting all of the sheets per my previous post, and deleting those you do not want.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com