How to Fix VBA runtime error 1004?

Microsoft has provided us with many useful applications from the early days. Due time the applications have become part of Microsoft Office. Microsoft Excel is...

Rahul Verma

Last updated on July 28, 2022

Rahul Verma

Author

Rahul is a Computer Science student with a massive interest in the field of Tech and cryptocurrency topics. He spends most of his time either writing or listening to music or traveling un-seen places. He believes that chocolate is the solution to all his problems. Life happens, and coffee helps.

Microsoft has provided us with many useful applications from the early days. Due time the applications have become part of Microsoft Office. Microsoft Excel is one such application that exists from the early days of the computer, and it is a program that allows users to organize format and calculate data. Moreover, it allows users to use formulas, utilizing a spreadsheet system. Microsoft Excel is used by numerous businesses all around the world to perform financial analysis. Even more, MS Excel also supports Visual Basic Applications, which means you can add a VBA script attached to your spreadsheet.

Other office programs and MS Excel utilize VBA code for numerous functions, for instance, you can add a button in the spreadsheet, which will add data automatically. Such functions are very useful when you are about to manage a large amount of data. However, users have often reported errors that they have a face. Here, we are discussing the particular VBA runtime error 1004. The possible reasons for 1004 VBA runtime error is invalid syntax or code, filtered data, corruption of MS Excel. However, it is also possible that two programs are in conflict while trying to open the VBA file at the same time.

Method 1. Create a New Template

By putting a new Excel sheet within a template rather than copying or duplicating the existing sheet may fix the issue. Therefore try this out first.

Step 1) Firstly, launch Microsoft Excel and make a new workbook then delete the present sheet, only leaving a single one. You can format as you want to design the default template. Click on File option from the menu bar and select Save As option.

How to Fix VBA runtime error 1004?

Step 2) Select the Excel Workbook option, a file explorer window will open up. Select the directory which you want to save your template.

How to Fix VBA runtime error 1004?

Step 3) Fill in the name of the template, then choose file type as .xlt for Excel 2003 and .xltx for excel 2007 or later. Click on the Save button, and your template will be saved. Now try to run the VBA code.

Method 2. Deleting the GWXL97.XLA file

If more than one program tries to open the VBA file at the same time, the conflict is sure to occur, therefore to ensure there is no conflict, you can try by deleting the GWXL97.XLA file.

Step 1) Open up the File Explorer.

Step 2) Navigate to the following directory-

C:\Program Files\MSOffice\Office\XLSTART

Step 3) You will see the GWXL97.XLA file in the folder, select and hit the delete button on your keyboard or right-click and select delete. Now try to run the VBA code.

Method 3. Debug the code

It is very possible that the 1004 error is occurring due to the invalid code. Therefore you will have to write the correct syntax and value for the VBA code to perfectly execute. There can be numerous mistakes that can be the result for 1004 error, to prevent this from happening you have to check your code twice and point out the invalid lines. For Example, The Name is Already Taken message is shown with the 1004 error when if the name of the worksheet already exists, and it is assigned to the same name to another.

If you run the code, it will generate the 1004 error message, therefore try to change the name and assign the values. There are more 1004 errors related to invalid VBA code, for example, a range of object_Global failed, Select Method of Range class field, and much more. Each time the message will be shown, the mistakes will be highlighted with a 1004 error if the error is related to the VBA code. Therefore debug the code with valid syntax and values.

Conclusion

The error code 1004 in Ms. Excel can be silly fixed by debugging the codes, fixing the invalid lined, names, and values. Moreover, if the issue is not related to code, then you can delete the XLA file that may be in conflict of MS Excel while opening the VBA file. Also, you can try to create a new template withing the existing Workbook to troubleshoot the issue.

Editor's Choice:

How to Fix VBA runtime error 1004?