Run time error 1004 method sheets of object global failed

We have a complex macro enabled spreadsheet. Today we got a ticket from the business user that Excel is throwing error:

“Run-time 1004 error: Method ‘Sheets’ of object ‘_Global’ failed.”.

I could not find an answer even after searching online for hours. None of the online discussion was giving the root cause of the error. Everyone were discussing how to change the code to fix the error.

The spreadsheet was working fine for couple of years but it suddenly stopped working. I suspected that it wasn’t the code but it was something else. I needed the root cause of this error.

So What is “Sheets” of Object “_Global”?

Finally, I found the root cause of this error at this link. Author discusses that Sheets is shortcut of Application.ActiveWorkbook.Sheets.

Now the error makes sense. In simple words, this error is trying to say that “Sheets” is trying to resolve to “Application.ActiveWorkbook.Sheets” but it encountered “Nothing” in the object chain.

So either “Application = Nothing” or “Application.ActiveWorkbook = Nothing”. As “Application” would always be available, the main culprit for this error is “Application.ActiveWorkbook”. Value of “Application.ActiveWorkbook” is “Nothing” and hence method “Application.ActiveWorkbook.Sheets” is not available which is the root cause of this error.

Why Application.ActiveWorkbook Becomes Nothing

Application.ActiveWorkbook represents currently open, visible and focused Excel spreadsheet.

Here are few reasons why Application.ActiveWorkbook becomes Nothing and hence it throws an error in VBA code.

• Excel file (i.e. active workbook) is in PROTECTED VIEW mode.
• Excel file (i.e. active workbook) is hidden.
• Excel file (i.e. active workbook) looses focus.

3 Solutions to Resolve This Issue

You may encounter one of the above issues. Depending on your issue, try following solutions depending on your situation.

• If Excel file is in PROTECTED VIEW, make sure to make it editable.
• If the Excel file is not visible, go to View > Windows > Unhide and make sure to unhide your Excel file.
• If for any reason your Excel file looses focus, make sure it is visible and the focus is on your current Excel file.

Solution For My Issue

In my case, VBA code was executing upon opening the file. I temporarily commented out the code which was throwing an error. Once the code executed, it still did not show the Excel file.

In Developer Mode, I could see the file is open but it was not visible. I figure out that somehow Excel file was accidentally hidden by the user.

I went to View Ribbon > Window > Unhide and unhide the Excel spreadsheet.

Once the file is visible, it successfully executed the code.

 Summary:

In this post, I have included the complete information about Excel runtime error 1004. Besides that I have presented some best fixes to resolve runtime error 1004 effortlessly.

To fix Runtime Error 1004 in Excel you can take initiatives like uninstalling Microsoft Work, creating a new Excel template, or deleting The “GWXL97.XLA” File. If you don’t have any idea on how to apply these methods then go through this post.

Here in this article, we are going to discuss different types of VBA runtime error 1004 in Excel along with their fixes.

What Is Runtime Error 1004 In VBA Excel?

Excel error 1004 is one such annoying runtime error that mainly encounters while working with the Excel file. Or while trying to generate a Macro in Excel document and as a result, you are unable to do anything in your workbook.

This error may cause serious trouble while you are working with Visual Basic Applications and can crash your program or your system or in some cases, it freezes for some time. This error is faced by any versions of MS Excel such as Excel 2007/2010/2013/2016/2019 as well.

Error Detail:

Error Code: run-time error 1004

Description: Application or object-defined error

Screenshot Of The Error:

Don’t worry you can fix this Microsoft Visual Basic runtime error 1004, just by following the steps mentioned in this post. But before approaching the fixes section catch more information regarding runtime error 1004.

Excel VBA Run Time Error 1004 Along With The Fixes

The lists of error messages associated with this Excel error 1004 are:

1. VB: run-time error ‘1004’: Application-defined or object-defined error
2. Excel VBA Runtime error 1004 “Select method of Range class failed”
3. runtime error 1004 method range of object _global failed visual basic
4. Excel macro “Run-time error ‘1004″
5. Runtime error 1004 method open of object workbooks failed
6. Run time error ‘1004’: Method ‘Ranger’ of Object’ Worksheet’ Failed
7. Save As VBA run time Error 1004: Application defined or object defined error

Let’s discuss each of them one by one…!

#1 – VBA Run Time Error 1004: That Name is already taken. Try a different One

This VBA Run Time Error 1004 in Excel mainly occurs at the time of renaming the sheet.

If a worksheet with the same name already exists but still you are assigning that name to some other worksheet. In that case, VBA will throw the run time error 1004 along with the message: “The Name is Already Taken. Try a different one.”

Solution: You can fix this error code by renaming your Excel sheet.

#2 – VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed

This VBA error code mainly occurs when someone tries to access the object range with wrong spelling or which doesn’t exist in the worksheet.

Suppose, you have named the cells range as “Headings,” but if you incorrectly mention the named range then obviously you will get the Run Time Error 1004: Method “Range” of object’ _ Global’ failed error.

Solution: So before running the code properly check the name of the range.

# 3 – VBA Run Time Error 1004: Select Method of Range class failed

This error code occurs when someone tries to choose the cells from a non-active sheet.

 Let’s understand with this an example:

Suppose you have selected cells from A1 to A5 from the Sheet1 worksheet. Whereas, your present active worksheet is Sheet2.

At that time it’s obvious to encounter Run Time Error 1004: Select Method of Range class failed.

Solution: To fix this, you need to activate the worksheet before selecting cells of it.

#4 – VBA Runtime Error 1004 method open of object workbooks failed

This specific run time Error 1004 arises when someone tries to open an Excel workbook having the same workbook name that is already open.

In that case, it’s quite common to encounter VBA Runtime Error 1004 method open of object workbooks failed.

Solution: Well to fix this, first of all close the already opened documents having a similar name.

#5 – VBA Runtime Error 1004 Method Sorry We Couldn’t Find:

The main reason behind the occurrence of this VBA error in Excel is due to renaming, shifting, or deletion of the mentioned path.

The reason behind this can be the wrong assigned path or file name with extension.

When your assigned code fails to fetch a file within your mentioned folder path. Then you will definitely get the runtime Error 1004 method. Sorry, and We couldn’t find it.

Solution: make a proper check across the given path or file name.

#6 – VBA Runtime Error 1004 Activate method range class failed

Behind this error, the reason can be activating the cells range without activating the Excel worksheet.

This specific error is quite very similar to the one which we have already discussed above i.e Run Time Error 1004: Select Method of Range class failed.

Solution: To fix this, you need to activate your excel sheet first and then activate the sheet cells. However, it is not possible to activate the cell of a sheet without activating the worksheet.

Why This Visual Basic Runtime Error 1004 Occurs?

Follow the reasons behind getting the run time error 1004:

1. Due to corruption in the desktop icon for MS Excel.
2. Conflict with other programs while opening VBA Excel file.
3. When filtered data is copied and then pasted into MS Office Excel workbook.
4. Due to application or object-defined error.
5. A range value is set programmatically with a collection of large strings.

Well, these are common reasons behind getting the VBA runtime error 1004, now know how to fix it. Here we have described both the manual as well as automatic solution to fix the run time error 1004 in Excel 2016 and 2013. In case you are not able to fix the error manually then make use of the automatic MS Excel Repair Tool to fix the error automatically.

Follow the steps given below to fix Excel run time error 1004 :

1: Uninstall Microsoft Work

2: Create New Excel Template

3: Delete The “GWXL97.XLA” File

Method 1: Uninstall Microsoft Work

1. Go to the Task Manager and stop the entire running programs.

2. Then go to Start menu > and select Control Panel.

3. Next, in the Control Panel select Add or Remove Program.

4. Here, you will get the list of programs that are currently installed on your PC, and then from the list select Microsoft Work.

5. And click on uninstall to remove it from the PC.

It is also important to scan your system for viruses or malware, as this corrupts the files and important documents. You can make use of the best antivirus program to remove malware and also get rid of the runtime error 1004.

Method 2: Create New Excel Template

Another very simple method to fix Excel runtime error 1004 is by putting a new Excel worksheet file within a template. Instead of copying or duplicating the existing worksheet.

Here is the complete step on how to perform this task.

1.Start your Excel application.

2. Make a fresh new Excel workbook, after then delete the entire sheets present on it leaving only a single one.

3. Now format the workbook as per your need or like the way you want to design in your default template.

4. Excel 2003 user: Tap to the File>Save As option

OR Excel 2007 or latest versions: Tap to the Microsoft Office button after then hit the Save As option.

5. Now in the field of File name, assign name for your template.

6. On the side of Save as type there is a small arrow key, make a tap on it. From the opened drop menu

• Excel 2003 users have to choose the Excel Template (.xlt)

• And Excel 2007 or later version have to choose the Excel Template (.xltx)

7. Tap to the Save.

8. After the successful creation of the template, now you can programmatically insert it by making use of the following code:
Add Type:=pathfilename

Remarks: 

From the above code, you have to replace the pathfilename with the complete path including the file name. For assigning the exact location of the sheet template you have just created.

Method 3: Delete The “GWXL97.XLA” File

Follow another manual method to fix Excel Runtime Error 1004:

1. Right-click on the start menu.

2. Then select the Explore option.

3. Then open the following directory – C:Program FilesMSOfficeOfficeXLSTART

4. Here you need to delete “GWXL97.XLA” file

5. And open the Excel after closing the explorer

You would find that the program is running fine without a runtime error. But if you are still facing the error then make use of the automatic MS Excel Repair Tool, to fix the error easily.

Automatic Solution: MS Excel Repair Tool

MS Excel Repair Tool is a professional recommended solution to easily repair both .xls and .xlsx file. It supports several files in one repair cycle. It is a unique tool that can repair multiple corrupted Excel files at one time and also recover everything included charts, cell comments, worksheet properties, and other data. This can recover the corrupt Excel file to a new blank file. It is extremely easy to use and supports both Windows as well as Mac operating systems.

* Free version of the product only previews recoverable data.

Steps to Utilize MS Excel Repair Tool:

Conclusion:

Hope this article helps you to repair the runtime error 1004 in Excel and recovers Excel file data. In this article, we have provided a manual as well as automatic solution to get rid of Excel run-time error 1004. You can make use of any solution according to your desire.

Good Luck!!!

VBA Runtime Error 1004 occurs while you are executing a macro in Excel. It’s an error that can occur due to several reasons. In the below example, as you can see, when I run the code, it shows the run-time error ‘1004’. In simple words, you can also say it occurs when you can’t perform an activity with the object or the application.

In this tutorial, we will understand several other reasons for this runtime 1004 error.

Error1: VBA Run Time Error 1004: That Name is already taken. Try a different One:

As I shared above, when you try to add a sheet with the name that is already there in the workbook using a VBA code, VBA shows you a run-time error with the 1004 code.

Even when you try to rename a worksheet, you’ll get the same error from VBA.

In the above code, I tried to rename the sheet “Sheet2” with the name “Sheet1”, but as you can see, I already have the “Sheet1” in the workbook; VBA showed me the error code 1004.

To deal with this problem is to check if a sheet already exists in the workbook or not.

Error2: VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed:

As you know, you can create a named range in Excel, but when you try to refer to that named range using a VBA code, and you misspelled its name, you are likely to get the run-time error 1004 Method “Range” of object’ _ Global’ failed.

In the following example, I have a named range with the name “myRange”, but while writing the macro, I have misspelled it as “myRang” even though it’s a typing mistake; VBA is not that smart to identify this mistake, and refer to the correct named range.

There is no named range with the name that I have mentioned, and that is why it showed me the run-time error 1004.

Error3: VBA Run Time Error 1004: Select Method of Range class failed:

When you use a VBA code to select a range that is not in the active worksheet, VBA will show you the run-time error 1004. Because, if you want to select a cell or range of cells for a particular worksheet needs to be active at that point.

You cannot select a cell from sheet1 if sheet2 is active right now.

As you can see in the following example, I have made the same mistake where I’m referring to cell A1 from sheet one, but sheet2 is active.

The best way to deal with this error is to activate the sheet first and then write a line of code that selects a cell or the range from that sheet. You can also check if a sheet is active or not and then select the cell from that sheet, and if somehow that sheet is not active, you can show a message to the user to activate that sheet first.

Error4: VBA Runtime Error 1004 Activate method range class failed:

Just like the above reason, when you try to activate a cell or range of cells from a specific worksheet that is not active at the moment, we will show you runtime error 1004 (Activate method range class failed:).

In the about example when I tried to activate cell A1 in the sheet1 it gave me runtime error 1004, is the active sheet was the sheet2.

To deal with this error again you need to verify that the worksheet that you are referring to is active or not. Or you can also verify if that sheet is there in the workbook or not.

Error5: VBA Runtime Error 1004 method Sorry We couldn’t Find:

If you’re trying to open an Excel file using a path, but that workbook is not there, maybe it has been moved, deleted, or renamed. In that case, VBA will again show you a run-time error of 1004.

That’s what happened in the following example where I tried to open the file with the path from my desktop, but that file is not there, and it has shown me an error.

To deal with the situation, you can use a VBA code to verify if the file that you are referring to is there in the folder or not.

Error6: VBA Runtime Error 1004 method open of object workbooks failed:

This error is also related to the workbook. So when you try to open a workbook with the name for which a book already exists and is open at the moment, VBA will show you the run-time error 1004 (method open of object workbooks failed).

The same thing happened in the above example, where I tried to open a workbook that is already open, and VBA showed me a run time error (1004).

How to Correct VBA’s Runtime (Error 1004)

Error 1004 is a run-time error that occurs while executing a VBA code. And all the errors that we have discussed above are due to human errors while writing the code, following the points you can use to avoid the run-time error 1004.

1. Do a step-by-step code execution to see on which line the error occurs.
2. Make sure to verify all the names of the workbooks that you are referring to.
3. You can also use a VBA code to verify if a workbook is already opened or not.
4. And use the activate method to activate a worksheet before you refer to a cell or range from it.

More on VBA Errors

Subscript Out of Range (Error 9) | Type Mismatch (Error 13) | Object Required (Error 424) | Out of Memory (Error 7) | Object Doesn’t Support this Property or Method (Error 438) | Invalid Procedure Call Or Argument (Error 5) | Overflow (Error 6) | Automation error (Error 440) | VBA Error 400

Are you searching for how to fix ‘Cannot run the macro’ in Excel? Then you’ve come to the right place. The message is one of many that may appear as a result of Runtime Error 1004. Other variations of the Excel Error 1004 include:

• Application-defined or object-defined error: Runtime Error 1004
• Method ‘Ranger’ of Object Worksheet Failed
• Copy method of worksheet class failed
• Programmatic access to Visual Basic Project is not trusted
• Excel macro “Run-time error 1004″
• Method open of object workbooks failed: Runtime Error 1004
• Select method of Range class failed: Excel VBA Runtime Error 1004
• Application-defined or object-defined error: Save As VBA Run Time Error 1004
• Method range of object _global failed visual basic: Runtime Error 1004

What Is the Runtime Error 1004 In Excel?

Microsoft Excel is a useful and highly demanded spreadsheet that makes data computation easy. It is used by both individuals and businesses all over the world and comes in XLS and XLSX format.

However, while working on an Excel file or trying to generate a Macro, many users have been aggravated by the Runtime Error 1004. It prevents you from making any changes in your Excel workbook, makes you unable to use certain resource libraries, and freezes or crashes the program or even your system. It causes annoying performance problems while working with Visual Basic applications.

The Runtime Error 1004 can occur on any version of Excel: from Excel 2003 to Excel 2019. It mostly appears when you try running Microsoft Visual Basic for Applications Macro. The problem is due to a security procedure that is meant to lock Microsoft VBA from Excel. The Runtime Error 1004 likely won’t occur if you grant security access. The error could also be due to program corruption as a result of malware infection.

Why Am I Getting the Excel Runtime Error 1004?

The Runtime Error 1004 can appear as a result of:

1. Corrupt MS Excel desktop shortcut.
2. Copying and pasting filtered data into an MS Office Excel workbook.
3. Program conflict while opening the VBA Excel file.
4. Programmatically setting a range of values with a collection of large string.
5. Application or object-defined error.

Whatever the reason may be, the issue is easy to get rid of. Keep reading to learn how to fix the Runtime Error 1004 when you make changes in Excel.

How to Fix the Runtime Error 1004 in Excel on Windows 10

The following solutions have been proven to work:

1. Run an antimalware scan
2. Allow Access to the VBA Project Object Model
3. Create a new Excel template
4. Uninstall Microsoft Works
5. Remove GWXL97.XLA

By the time you have tried one or a few of the above fixes, the error will be resolved. We will now provide you with detailed steps to carry out the solutions.

Fix 1: Run an Antimalware Scan

Fix 2: Allow Access to the VBA Project Object Model

The procedure for this solution is easy. Follow the steps below:

1. Launch Excel and click on the File tab.
2. Click on Options.
3. Click on Trust Center. It is the last option in the left pane.
4. Click the Trust Center Settings button in the right pane.
5. In the new page that opens, locate Macro Settings in the left pane and click on it.
6. Mark the checkbox for ‘Trust access to the VBA project object model’ under the Developer Macro Settings section in the right pane.
7. Click the OK button.

Fix 3: Create a New Excel Template

Rather than copy or duplicate your existing worksheet, you can put your new Excel Worksheet file within a template. This will help fix the Runtime Error 1004. Here’s how to get it done:

1. Launch Excel.
2. Press the Ctrl + N combination on your keyboard to open a new workbook. Make sure to leave only one sheet on the workbook. Delete the rest.
3. Modify the workbook to suit your needs.
4. Save the workbook by pressing Ctrl + S on your keyboard. Or if you use Excel 2003, click on the File tab and click Save as. For Excel 2007 and newer versions, click the Microsoft Office icon in the top-left corner of your screen and then click on Save as.
5. Choose a location to save the file on your computer and enter a name for the file.
6. Click the drop-down arrow beside ‘Save as type’ and select Excel Template (.xlt) if you are using Excel 2003. If you are using Excel 2007 and later versions, select Excel Template (.xltx) instead.
7. After saving the template, use the following code to insert it:

Add Type:=pathfilename

Make sure to replace “pathfilename” with the actual path and file name of the template.

Fix 4: Uninstall Microsoft Works

Here’s the procedure to follow:

1. Right-click on the Start button to open the Power User menu. Select Task Manager from the list. Alternatively, press the Ctrl + Alt + Del combination on your keyboard and then click on Task Manager.
2. Once in Task Manager, click on each of the running apps and click the End Task button.
3. Close the Task Manager window.
4. Open the Run utility by pressing the Windows + R keyboard combination. Type “appwiz.cpl” (no quotes) into the text field and click the OK button or press Enter on your keyboard.
5. Locate Microsoft Works in the list of apps and right-click on it. Then, click on Uninstall from the context menu. Confirm the action if prompted.

Fix 5: Remove GWXL97.XLA

Here’s how to delete the GWXL97.XLA file:

1. Press the Windows + E combination on your keyboard to open File Explorer.
2. Navigate this path: C: Users > User > Name > AppData > Local> MicrosoftExcel.
3. Open the XLStart folder.
4. Locate and delete the GWXL97.aXLA file.

Conclusion

We hope the solutions provided above have helped you to successfully fix the Runtime Error 1004 in Microsoft Excel and recover the data on your file. There are many issues that can lead to the runtime error. However, by the time you get to the end of this guide, you will be just fine.

Don’t hesitate to leave your questions or comments in the section below. We love hearing from you.