How to Fix Run Time Error 1004 in Excel

Run-time error 1004 is an error code pertaining to Microsoft Visual Basic that has been known to affect Microsoft Excel users. The error is known to be most prevalent on Excel 2003 and Excel 2007, although no version of Microsoft’s popular worksheet application for computers is safe from the menace that is Run-time error 1004. In most cases, users affected by this problem see one of two different variations of Run-time error 1004. In their entirety, the two variations of Run-time error 1004 read:

“Run-time error ‘1004’:
Copy Method of Worksheet Class failed”

“Run-time error ‘1004’:
Application-defined or object-defined error”

The exact error message you see might also vary slightly, in a few cases, although the error code will remain the same. Regardless of which version of the issue you experience, however, it almost always occurs while running a macro in Excel that is designed to copy worksheets and then place the copies into the same workbook as the original worksheet.

Unlike the worst tech problems out there, the cause of Run-time error 1004 within Microsoft Excel is known. Run-time error 1004 occurs when the macro you are running is copying the original worksheet to a workbook with a defined name that you did not save and close before you ran the macro. An example of such a macro can be seen in the following 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

Since the cause of Run-time error 1004 in the context of Microsoft Excel is known, as is the resolution. In addition to a solution to this issue, what is also available to users affected by it is a workaround that can be used in the event that the solution doesn’t work or they don’t find the solution to be a viable option.

The Solution:

The solution to this specific problem is to simply edit the code of the macro you are running to have it periodically save and close the target workbook while it makes copies of the worksheet(s). The code for a macro that does this would look something along the lines of the following:

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 exact number of times you can copy of a worksheet before you need to save and close the workbook the copies are being saved to varies from one case to the next as it depends on the size of the worksheet you are making copies of.

The Workaround:

As stated before, a workaround to this specific problem also exists. Working around this issue is a simple matter of inserting a new worksheet from a template instead of creating a copy of an existing worksheet. If you would like to work around this problem, here’s what you need to do:

1. Launch Excel.
2. Create a new workbook, and delete every single worksheet the workbook contains save for one.
3. Format the workbook.
4. Add any text, data and/or charts you wish to have in the template by default to the only worksheet the workbook now contains.
5. If you are using Excel 2003 or earlier, click on File > Save As. If you are using Excel 2007 or later, on the other hand, click on the Microsoft Office button and then click on Save As.
6. In the File name: field, type in whatever you want the template to be called.
7. Open the dropdown menu next to the Save as type: field and click on Excel Template (.xlt) if you are using Excel 2003 or earlier, or Excel Template (.xltx) if you are using Excel 2007 or later to select it.
8. Click on Save.
9. Once you have successfully created the template, you can insert it programmatically by using the following line of code:
   Sheets.Add Type:=path\filename

Note: In the line of code described above, path\filename needs to be replaced with the full path (inclusive of the complete file name) for the location of the sheet template you have just created.