How to fix Excel error 1004

Run-time error 1004 is a Microsoft Visual Basic error code that has been known to impact users of Microsoft Excel. Although the problem is most common in Excel 2003 and Excel 2007, no version of Microsoft’s popular spreadsheet programs for PCs is immune to the threat of Run-time error 1004. In most situations, users who are impacted by this issue receive one of two Run-time error 1004 variants. In certain situations, the actual error message you receive may differ somewhat, but the error code will stay the same. Regardless of which version of the issue you experience, however, it almost always occurs while running a macro in Excel that is designed to copy worksheets and then place the copies into the same workbook as the original worksheet. This error is generated by a security setting in Excel that prevents programmatic access to Microsoft Visual Basic for Applications (VBA) unless the user explicitly allows it. In this blog post, we will address this error code and find a few solutions to both combat this error and help resolve it in its entirety. Follow the step-by-step process below to see how you can resolve this error.

Step by step process – How to fix Excel error 1004

  1. Open your Excel spreadsheet.
  2. Now select “File” from the drop-down menu.
  3. After that, select “Options.”
  4. Now choose “Trust Center.”
  5. Select “Trust Center settings”.
  6. Click on “Macro Settings” from the side menu.
  7. Make sure Trust access to the VBA project object model is checked in Macro Settings.
  8. Then click on “OK”.

Following the steps above will allow excel to gain access to the VBA projects you have created and create an access point to allow the project to be either submitted to your excel document or be edited in the VBA database which can be accessed through the developer. To prevent any security issues the VBA trust access can be disabled upon request and can be the reason the error code shows up. Following the steps above will mitigate this issue and allow you to resolve this error.

Since the source of Run-time error 1004 in Microsoft Excel is known, the remedy is also known. In addition to a remedy for this problem, people who are impacted by it have a workaround that they can employ if the solution does not work or they do not consider the solution to be a realistic choice.

Each runtime error has its own set of symptoms that are unique to that error code. We have addressed the most common type that users will most likely experience if they ever try to engage in the Macro setting portal. The error 1004 is most seen when using or attempting to use Microsoft Excel. The issue is generally accompanied by one of the various error messages listed above when it happens.

Reasons for error code 1004

The causes of runtime error 1004 are many, ranging from setup difficulties to software issues; a summary of these causes is as follows.

  • The user tries to copy and paste filtered data into a Microsoft Excel spreadsheet.
  • The value of a programmed range has been set improperly.
  • The starting symbol for Microsoft Excel has gotten corrupted.
  • When attempting to open a VBA Excel file, a conflict arises between one or more applications.

Alternative fix for error code 1004

Save workbooks and files

When dealing with macros, people frequently encounter the runtime error 1004. It is possible to execute macros in Excel that copy or duplicate worksheets and then place them in the same workbook where they were created.

This operation may result in an error 1004, with the user receiving a notice stating that the method of copying the worksheet failed, or that an object-defined error or application-defined error has occurred.

The user frequently causes this problem by giving the workbook a name and copying it without first closing the workbook.

Simply save your workbook regularly before the copying procedure to overcome this problem. It’s worth noting that the number of times a worksheet may be copied or cloned before requiring a save is determined by the file’s size.

Using a registered software cleaner

Because registry conflicts are the most common cause of runtime problems, conducting a complete scan of your system using a reputable registry cleaner may usually resolve the problem.

Because registry conflicts are the most common cause of runtime problems, conducting a complete scan of your system using a reputable registry cleaner may usually resolve the problem.

Anyway, we highly recommend that you download and install Advanced System Repair Pro, which is one of the most renowned registry cleaners on the market, for this specific purpose.

In depth – Step by step process – How to fix Excel error 1004

Step by step breakdown:

  • Open your Excel spreadsheet.

This document can be anything you want, it can be a document you are currently working on with the VBA input or in this example we have just opened a blank worksheet to show you the process.

  • Now select “File” from the drop-down menu.

The file will be located in the top ribbon next to the home. This is where a majority of your settings for both the workbook and account are. You can use this to edit the trust feature and ensure the error does not show again.

  • After that, select “Options.”

The option will not open a window rather it will open a dialogue box for you to edit preferences.

  • Now choose “Trust Center.”

The Trust Center is where you can find security and privacy settings for Microsoft Office programs. Because the ribbon appears in all Office products in the same way, the procedures to discover the Trust Center is the same.

  • Select “Trust Center settings”.
  • Click on “Macro Settings” from the side menu.
  • Make sure “Trust access to the VBA project object model” is checked in Macro Settings.
  • Then click on “OK”.

That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.

Saajid Gangat

Saajid Gangat has been a researcher and content writer at Business Tech Planet since 2021. Saajid is a tech-savvy writer with expertise in web and graphic design and has extensive knowledge of Microsoft 365, Adobe, Shopify, WordPress, Wix, Squarespace, and more! You can connect with Saajid on Linkedin.

Recent Posts