Home News How To Easily Fix Runtime Error 1004 In Excel

How To Easily Fix Runtime Error 1004 In Excel



Recently, some of our readers informed us that they are experiencing runtime error 1004 in Excel.



  • 2 minutes to read.
  • 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

    run-time error 1004 in excel

    Error screenshot:

    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.



    How do I fix Visual Basic error in Excel?

    Troubleshooting Steps
    1. Start Microsoft Excel.
    2. From the menu bar, select File> [Excel] Options> Trust Center> Trust Center Options> Macro Options.
    3. Activate the switch “Disable all macros with notification”
    4. at the top

    How do you fix a run time error in Excel?

    In Excel, select the “File” tab on the taskbar and left-click “Open” in the provided list. Now click on the file you want and select “Open” -> “Open and Restore” -> “Restore” (you can also select the “Extract Data” tab). If all goes well, runtime error 13 will no longer bother you.