Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

  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.

↑ Back to the top


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top


Keywords: kbmacro, kbautomation, kbvba, kbexpertiseinter, kbbug, kberrmsg, kbpending, kbprogramming, KB210684

↑ Back to the top

Article Info
Article ID : 210684
Revision : 8
Created on : 9/18/2011
Published on : 9/18/2011
Exists online : False
Views : 458