VBA 1004 Error

What is VBA Error 1004 in Excel?

Visual basic for applications (VBA) “Run-time error 1004” occurs when we can’t perform an activity with the application. It is shown as an “Application-Defined or Object-Defined error.”

This error occurs when a user is creating a macro in Excel. There are several other reasons for VBA errors in Excel. For example, “VBA Run time Error 1004: Sorry we couldn’t find the file named “XYZ”. Is it possible it may be removed, renamed, or moved.” Another reason this error usually arises is when a user tries to open an unavailable workbook or if they have specified a wrong path.

Enabling VBA in Excel

Before looking into the “Run-time Error 1004” types and their causes, let’s see how to enable VBA in Excel.

Step 1: Open an Excel sheet and right-click anywhere on the excel ribbon. Select the “Customize the Ribbon “option.

Step 2: In the “Customize the Ribbon” dialog box, check the “Developer” option.

Step 3: To open the VBA editor, click the Developer tab and the “Visual Basic” option.

Step 4: A “Microsoft Visual Basic for Applications” window will open. Click “View code,” to type any VBA code in the editor.

As we have learned how to enable VBA in Excel, let us see the different types of “VBA Run-time Error1004” and their possible reasons.

Top 6 Excel VBA 1004 Runtime Errors

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

The VBA 1004 error generally occurs when we use the same name for two Excel worksheets in the same workbook.

Example:  Here, Sheet1 has been named “Students.” When we try to rename Sheet2 as “Students” as well, this will cause the VBA Run-time Error 1004. 

Step 1: Name the Excel sheet1 as “Students” by clicking on “Sheet1” at the bottom. Now, try to rename Sheet2 with the same name.

Step 2: Go to the “Developer” tab and click the “Visual Basic” option—the Visual Basic editor opens.

Step 3: State the Sub-function to write the code.

Code:

Sub Error1004Ex_1()
End Sub

Step 4: Now, rename Sheet2 as “Students” by writing the below code:

Code:

Sub Error1004Ex_1()
Worksheets(“Sheet2”). Name= “Students”
End Sub

Step 5: Run the code shown above. You will get the “Run-time Error 1004-That name is already taken. Try a different one.” VBA 1004 error, as seen below.

Therefore, rename the sheets accordingly to avoid this error.

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

This error generally occurs when we try to access a named range value incorrectly in VBA.  For example, it appears when there is a spelling mistake when typing the named range. To understand this, let us create a named range in Excel with the following data.

Step 1: Type the data set and name the table header as Headings.

Step 2: Click on the “Developer” tab and select “Visual Basic” to open the VB Editor.

Step 3: State the sub-function for writing the code.

Name the Header as Headings to select the range.

Code:

Sub Error1004Ex_1()
Range (“Headings”). Select
End Sub

Step 4: Run the code and check if the correct value range in Excel is selected.

Step 5: Now, if we misspell the Header name in the code as “Heaings,” we get the error, ”Method “Range” of object_Worksheet failed.”

Code:

Sub Error1004Ex_1()
Range (“Heaings”). Select
End Sub

The error arises after we run the code.

Therefore, use the correct spellings in the code to avoid such errors.

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

This VBA 1004 error usually occurs when we try to use the value from Sheet1 in Sheet2 without activating Sheet1. Let’s see how it happens with an example.

Step 1: Click on the “Developer” tab and select “Visual Basic” to open VB Editor.  State the sub-function for writing the code.

Code:

Sub Error1004Ex_1()
Worksheets(“Sheet1”).Range(“A2:A6”).Select
End Sub

Step 2: The above code selects the range from A2 to A6 from Sheet1 to the currently active sheet Sheet2. Thus, an error arises when we run the code, as shown below.

Therefore, to avoid this error, activate the sheet where we must select the data.

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

This VBA 1004 error occurs when we try to open a workbook having the same name as an already opened workbook.

Step 1: Type the following code.

Code:

Sub Error1004_Example()
Dim A As Workbook
Set wb = Workbooks.Open(“VBA 1004 Error.xlsm”, Readonly:=True, Corruptload:=xlExtractData)
End Sub

Step 2: Run the code; we will see the result below.

Thus, to prevent this error, either rename the workbook or close it first and then open it.

Example #5 – VBA Runtime Error 1004 method Sorry We couldn’t Find:

This error is like the above error to some extent. However, this error occurs because it cannot find the workbook here as it doesn’t exist. Here, we try to open a workbook that is deleted by using the VBA code shown below.

Code:

Sub Error1004_Example()
Workbooks.Open Filename:=”E:Excel SizeestimationNPs.xlsx”
End Sub

When you run the VBA code, you can see the Run-time 1004 Error: “Sorry we couldn’t find the file; it may be removed, moved, or renamed.”

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

This error appears when you use a cell or the range of cells from a worksheet without activating it.

Code:

Sub Error1004_Example()
Worksheets(“Sheet2”).Range(“A2:A4”).Activate
End Sub

Run the VBA code and we will see the error as shown below:

Therefore, activate the sheet before selecting its values and then run the code.

Important Things to Note

For VBA error handling, always remember these points.

• Check for spelling mistakes in your range name, worksheet name, etc.
• Always select the value range from an activated workbook.
• Do not rename sheets in a workbook with the same name.
• Ensure the path provided for the workbook to open is correct.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA 1004 Error, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA 1004 Error. Here we explain the top six Run-time Error 1004 with examples and downloadable excel template. You may learn more from the following articles –

• VBA FileSystemObject
• VBA IIF
• VBA Set Statement