VBA 1004 Error

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

VBA 1004 Error is a runtime error in VBA. It is also known as application-defined or object-defined error. Why is that? Because we have a limited number of columns in Excel. When our code gives the command to go out of range, we get a 1004 Error. There are other situations when we get this error when we refer to a range that does not exist in the sheet.

VBA Error 1004 in Excel

VBA 1004 Error is a run time error in VBA and occurs while running the code. Errors are part and parcel of the coding, especially when writing for the first time. Therefore, you may come across many errors in VBA. However, this is common for everybody, and there is no big deal about it.

However, knowing the error of why it is coming makes you avoid those mistakes in the coming future.

In this article, we will discuss one of the important errors in Excel, “VBA 1004 Error”.

VBA 1004 Error

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA 1004 Error (wallstreetmojo.com)

Top 6 Excel VBA 1004 Runtime Errors

You can download this VBA 1004 Error Template here – VBA 1004 Error Template

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

This error occurs while renaming the sheet.

If the worksheet’s name already exists. If you try to assign the same name to another sheet, VBA throws a “Run-time error ‘1004.’ ”

Look at the below code.

Code:

Sub Error1004_Example()

 Worksheets("Sheet2").Name = "Sheet1"

End Sub
VBA 1004 Error Example 1

We are trying to rename sheet 2 as sheet 1. But, we already have a sheet named “Sheet1”.

VBA 1004 Error Example 1-2

If we run this code using the F5 key or manually, we will get “Run-time error ‘1004’ “: “That name is already taken. Try a different one.”

VBA 1004 Error Example 1-1

So, try renaming the sheet accordingly.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

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

It usually occurs when we try to access the named range in excelNamed Range In ExcelName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window.read more with a spelling mistake or that does not exist you are referring to.

For this, we have named the range of cells “Headings,” as shown in the below image.

VBA 1004 Error Example 2

Now, by using the RANGE object, we can access this range.

Code:

Sub Error1004_Example()

  Range("Headings").Select

End Sub
VBA 1004 Error Example 2-1

If you run this code by pressing the F5 key, then this code will select the named range.

VBA 1004 Error Example 2-2

But, if we mention the named range wrongly, we will get “Run-time error ‘1004.’ “Method “Range” of object’ _ Global” failed.

Code:

Sub Error1004_Example()

  Range("Headngs").Select

End Sub
VBA 1004 Error Example 2-3

Run this code manually or use the F5 key and see the result.

VBA 1004 Error Example 2-4

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

It usually occurs when we try to select the cells other than the active sheet without making the sheet select or active.

 Look at the below code.

Code:

Sub Error1004_Example()

  Worksheets("Sheet1").Range("A1:A5").Select

End Sub
VBA 1004 Error Example 3

The above code says to select the cells A1 to A5 in the worksheet “Sheet1”. However, to experiment, my present active sheet is “Sheet2”, not “Sheet1”.

We will run this code using the F5 key or manually to see what happens.

VBA 1004 Error Example 3-1

We got “Run-time error ‘1004.’ “Select method of Range class failed” as without activating the sheet; we try to select the cells of that sheet. So first, we need to activate the sheet before we select the cells. Below is the correct code.

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

It usually occurs when you try to open the workbook, which has the same name as the other workbook we have already opened.

Look at the below code.

Code:

Sub Error1004_Example()

   Dim wb As Workbook
    Set wb = Workbooks.Open("FileName.xls", ReadOnly:=True, CorruptLoad:=xlExtractData)

End Sub
Example 3-2

It will throw the below error.

Example 3-3

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

This error occurs when you try to open the file which does not exist in the mentioned path. For example, it could move, be renamed, or deleted from the mentioned path. One reason for this is the wrong type of path or file name with the excel extensionExcel ExtensionExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code.read more.

Now, take a look at the below code.

Code:

Sub Error1004_Example()

   Workbooks.Open Filename:="E:Excel FilesInfographicsABC.xlsx"

End Sub
Example 4

This code says to open the file “ABC.xlsx” in the mentioned folder path.

We know there is no file in the mentioned folder path. So, we will get the: Run-time error ‘1004’ method when no file exists in the mentioned folder. Sorry, and We couldn’t find it.

Example 4-1

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

This error occurs mainly due to activating the range of cells without activating the worksheet.

Look at the below code.

Code:

Sub Error1004_Example()

  Worksheets("Sheet1").Range("A1:A5").Activate

End Sub
Example 5

This error is similar to the one we have seen in Run-time error’ 1004′: Select method of Range class failed.

If we run manually or use the F5 key, we will get the below error.

Example 5-1

Because without activating the sheet, we cannot activate the cells in it. So first, activate the sheet and then activate the cells of that sheet.

Recommended Articles

This article has been a guide to VBA 1004 Error. Here, we discuss the top 6 types of 1004 run-time errors in VBA and how to fix them, along with examples and downloadable templates. Below are some useful articles related to VBA: –