Microsoft KB Archive/210684

= Copying worksheet programmatically causes run-time error 1004 in Excel =

Article ID: 210684

Article Last Modified on 4/2/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



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) 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
 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:= \

In this code, \  is a string that contains the full path and file name for your sheet template.</li></ol>

Microsoft Office Excel 2003 and earlier versions of Excel
<ol> Create a new workbook, and then delete all of the worksheets except for one.</li> Format the workbook, and then add any text, data, and charts that you must have in the template by default.</li> Click File, and then click Save As.</li> In the File name box, type the name that you want for the Excel template.</li> In the Save as type list, click Template (*.xlt), and then click Save.</li> To insert the template programmatically, use the following code:

Sheets.Add Type:= \

In this code, \  is a string that contains the full path and file name for your sheet template.</li></ol>

<div class="status_section">

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

Keywords: kbprogramming kbmacro kbautomation kbvba kbexpertiseinter kbbug kberrmsg kbpending KB210684

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.