You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

Copying worksheet programmatically causes run-time error 1004 in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q210684
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
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)            NextEnd Sub				
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    NextEnd Sub				
Note The number of times you can copy a worksheet before you must save the workbook varies with the size of the worksheet.
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

  1. Create a new workbook, and then delete all of the worksheets except for one.
  2. Format the workbook, and then add any text, data, and charts that you must have in the template by default.
  3. Click the Microsoft Office Button, 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 (*.xltx), and then click Save.
  6. 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

  1. Create a new workbook, and then delete all of the worksheets except for one.
  2. Format the workbook, and then 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
    In this code, path\filename is a string that contains the full path and file name for your sheet template.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 210684 - Last Review: 09/18/2011 22:53:00 - Revision: 8.0

  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
  • kbprogramming kbmacro kbautomation kbvba kbexpertiseinter kbbug kberrmsg kbpending KB210684
Feedback
>ript' src='" + (window.location.protocol) + "//c.microsoft.com/ms.js'><\/script>");