WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA 1004 Error

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

VBA 1004 Error

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 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 example, look at the below code.

Code:

Sub Error1004_Example()

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

End Sub

VBA 1004 Error Example 1

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

VBA 1004 Error Example 1-2

If I run this code using the F5 key or manually, I 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.

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

This usually occurs when we try to access the named range in excel 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA 1004 Error Example 2

Now by using the Range object, I 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 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

VBA 1004 Error Example 2-3

Run this code manually or using 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:

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

For example, 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”. To experiment, my present active sheet is “Sheet2”, not “Sheet1”.

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

Example 3-2

This will throw the below error.

Example 3-3

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

Now take a look at the below code.

Code:

Sub Error1004_Example()

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

End Sub

Example 4

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 that exists in the mentioned folder, we will get the Runtime Error 1004 method. 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.

For example, look at the below code.

Code:

Sub Error1004_Example()

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

End Sub

Example 5

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 the F5 key, then we will get 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 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 –

  • VBA LEN Function
  • VBA Type Mismatch Error
  • VBA Sub Procedures
  • VBA Match Function
1 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA 1004 Error Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More