Recently, some of our readers informed us that they are experiencing runtime error 1004 in Excel.
- Applies to:
- Microsoft Excel
What Is Runtime Error 1004 In VBA Excel?
Excel Error 1004 is one of those annoying runtime errors that mainly occur when working with an Excel file. Or, if you try to create a macro in your Excel document, you won’t be able to do anything in your workbook.
This error can cause serious problems when using Visual Basic applications and cause your program or system to crash. In some cases it freezes for a while. This error also occurs in all versions of MS Excel, eg. B. Excel 2007/2010/2013/2016/2019.
Error code: Runtime error 1004
Description: Application or Object Error
Don’t worry, you can fix this Microsoft Visual Basic 1004 Runtime Error simply by following the steps in this article. However, before moving on to the fixes section, please read moreMore about runtime error 1004.
The solution to this particular problem is to simply change the code of the macro you run so that it periodically saves and closes the target workbook when making copies of the worksheets. The macro code that does this looks like this:
Sub CopySheetTest () Dim iTemp as integer Dim O considered as a binder 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 distinguished name to the book 'which belongs to the series: oBook.Names.Add Name: = "tempRange", _ RefersTo: = "= Sheet1! $ A $ 1" 'Save the book: oBook.Save as "c: test2.xls" 'Copy the sheet into the loop. After all, 'You get error 1004: Copy method from “Class worksheet failed. For iCounter = 1 to 275 oBook.Worksheets (1) .Copy After: = oBook.Worksheets (1) 'Comment out this code for a workaround: '' Save, close and reopen every 100 iterations: If iCounter Mod 100 = 0, then oBook.Close SaveChanges: = True Install oBook = nothing Set oBook = Application.Workbooks.Open ("c: test2.xls"). End if followingEnd of subroutine
Note. The exact number of times you can copy a worksheet before you need to save and close the workbook in which the copies are saved varies from case to case as it depends on the size of the worksheet. calculation that you create Copies.