Article ID: 210684 - View products that this article applies to.
This article was previously published under Q210684
Expand all | Collapse all

On This Page

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)        
    Next
End 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
    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.

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: September 18, 2011 - Revision: 8.0
APPLIES TO
  • 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
Keywords: 
kbprogramming kbmacro kbautomation kbvba kbexpertiseinter kbbug kberrmsg kbpending KB210684

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com