VBA 1004 Error

VBA 1004 Error is a runtime error in VBA which is also known as application-defined or object-defined error and why is that because we have limited number of columns in excel and when our code gives the command to go out of range we get 1004 error, there are other situations when we get this error when we refer to a range which 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 you are writing for the first time you may come across many errors in VBA. This is common for everybody and there is no big deal about it.

However to know 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 error Excel “VBA 1004 Error”.

Top 6 Excel VBA 1004 Runtime Errors

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

This error occurs while renaming the sheet.

If the name of the worksheet already exists and if you try to assign the same name to another sheet, VBA throws Run Time Error of 1004 stating “The Name is Already Taken. Try a different one”

For an example look the below code.

Code:

Sub Error1004_Example()

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

End Sub

I am trying to rename the sheet 2 as sheet 1. But I already have a sheet named “Sheet1”.

If I run this code using F5 key or manually, I will get Run Time Error 1004: That Name is already taken. Try a different One

So, try renaming the sheet accordingly.

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

This usually occurs when we try to access the named range with a spelling mistake or that doesn’t exist at all in the worksheet you are referring to.

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

Now by using Range object, I can access this range.

Code:

Sub Error1004_Example()

  Range("Headings").Select

End Sub

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

But if I mention the named range wrongly I will get Run Time Error 1004: Method “Range” of object’ _ Global’ failed

Code:

Sub Error1004_Example()

  Range("Headngs").Select

End Sub

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

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

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

For an example look at the below code.

Code:

Sub Error1004_Example()

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

End Sub

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

I will run this code uisng F5 key or manually to see what happens.

We got Run Time Error 1004: Select Method of Range class failed. This because without activating the sheet we try to select the cells of that sheet. So first we need to activate the sheer before we select the cells. Below is the correct code.

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

This usually occurs when you try to open the workbook which is the same name as the other workbook which is already opened.

For an example 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

This will throw the below error.

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

This error occurs due to when you try to open the file which does not exist in the mentioned path, this could be move, renamed, or deleted from the mentioned path. One of the reasons for this because of the wrong type of the path or file name with extension.

Now take a look at the below code.

Code:

Sub Error1004_Example()

   Workbooks.Open Filename:="E:\Excel Files\Infographics\ABC.xlsx"

End Sub

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

For sure I know there is no file in the mentioned folder path. When there is no file exists in the mentioned folder we will get Runtime Error 1004 method Sorry We couldn’t Find.

#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.

For an example look at the below code.

Code:

Sub Error1004_Example()

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

End Sub

This error is very similar to the one we have seen in Run Time Error 1004: Select Method of Range class failed.

If I run manually or using F5 key then we will get below error.

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 has been a guide to VBA 1004 Error. Here we discuss the top 6 types of 1004 Runtime Error in VBA and how to fix it along with examples & downloadable templates. Below are some useful articles related to VBA –

• Type Statement in Excel VBA
• VBA LEN Function
• What is VBA Type Mismatch Error?
• VBA Sub Procedures in Excel
• VBA Match Function Use
• What is Color Index in VBA?
• Use Date Function in VBA
• Worksheet Function in VBA List