Copying worksheet programmatically causes run-time error 1004 in Excel

View products that this article applies to.

Symptoms

In Microsoft Excel, you run a macro that copies worksheets and then places the worksheets into the same workbook from which they originated. When you do this, you may receive an error message that resembles one of the following error messages:

Run-time error '1004':
Copy Method of Worksheet Class failed

Run-time error '1004':
Application-defined or object-defined error

↑ Back to the top

Cause

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

This problem can occur when you give the workbook a defined name and then copy the worksheet several times without first saving and closing the workbook, as in the following sample code:

Sub CopySheetTest()
    Dim iTemp As Integer
    Dim oBook As Workbook
    Dim iCounter As Integer
    
    ' Create a new blank workbook:
    iTemp = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Set oBook = Application.Workbooks.Add
    Application.SheetsInNewWorkbook = iTemp
    
    ' Add a defined name to the workbook
    ' that RefersTo a range:
    oBook.Names.Add Name:="tempRange", _
        RefersTo:="=Sheet1!$A$1"
            
    ' Save the workbook:
    oBook.SaveAs "c:\test2.xls"
    
    ' Copy the sheet in a loop. Eventually,
    ' you get error 1004: Copy Method of
    ' Worksheet class failed.
    For iCounter = 1 To 275
        oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)        
    Next
End Sub

↑ Back to the top

Resolution

To resolve this problem, save and close the workbook periodically while the copy process is occurring, as in the following sample code:

Sub CopySheetTest()
    Dim iTemp As Integer
    Dim oBook As Workbook
    Dim iCounter As Integer
    
    ' Create a new blank workbook:
    iTemp = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Set oBook = Application.Workbooks.Add
    Application.SheetsInNewWorkbook = iTemp
    
    ' Add a defined name to the workbook
    ' that RefersTo a range:
    oBook.Names.Add Name:="tempRange", _
        RefersTo:="=Sheet1!$A$1"
            
    ' Save the workbook:
    oBook.SaveAs "c:\test2.xls"
    
    ' Copy the sheet in a loop. Eventually,
    ' you get error 1004: Copy Method of
    ' Worksheet class failed.
    For iCounter = 1 To 275
        oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
        'Uncomment this code for the workaround:
        'Save, close, and reopen after every 100 iterations:
        If iCounter Mod 100 = 0 Then
            oBook.Close SaveChanges:=True
            Set oBook = Nothing
            Set oBook = Application.Workbooks.Open("c:\test2.xls")
        End If
    Next
End Sub

Note The number of times you can copy a worksheet before you must save the workbook varies with the size of the worksheet.

↑ Back to the top

Workaround

To work around this problem, insert a new worksheet from a template instead of copying an existing worksheet. To do this, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Office Excel 2007

Create a new workbook, and then delete all of the worksheets except for one.
Format the workbook, and then add any text, data, and charts that you must have in the template by default.
Click the Microsoft Office Button, and then click Save As.
In the File name box, type the name that you want for the Excel template.
In the Save as type list, click Template (*.xltx), and then click Save.
To insert the template programmatically, use the following code:
Sheets.Add Type:=path\filename
In this code, path\filename is a string that contains the full path and file name for your sheet template.

Microsoft Office Excel 2003 and earlier versions of Excel

Create a new workbook, and then delete all of the worksheets except for one.
Format the workbook, and then add any text, data, and charts that you must have in the template by default.
Click File, and then click Save As.
In the File name box, type the name that you want for the Excel template.
In the Save as type list, click Template (*.xlt), and then click Save.
To insert the template programmatically, use the following code:
Sheets.Add Type:=path\filename
In this code, path\filename is a string that contains the full path and file name for your sheet template.