In this article, we are going to learn about Run Time Error 1004: Select Method of Range Class Failed. This is one of the most common errors that users encounter while applying VBA codes. This happens when users run a code on a sheet that is not active. This interrupts the smooth operation of the Excel VBA. So, by learning the reason and possible solution to this error, users can avoid it and also resolve it accordingly.
The below video illustrates the error clearly. As our expected “Employee” sheet is inactive, we get this error after running the code.
Download Practice Workbook
You can download the practice book here.
How to Launch VBA Macro Editor in Excel
In order to run any VBA code, we first need to write or edit the code in the VBA Macro Editor.
Before that, we need to enable the Developer tab, which remains hidden by default. The Developer tab allows users to access the VBA Macro Editor. Do the following tasks.
- Go to Developer Tab >> Visual Basic.
- This will open the Visual Basic window.
- Select Insert >> Module in the macro editor.
- As a result, an empty module will appear on the screen where you can write the code. However, use the Run icon or F5 key to run the code.
What Exactly Is Run Time Error 1004?
Run Time Error 1004 occurs in VBA (Visual Basic for Applications) when an attempt is made to select a range of cells that does not exist or is invalid. This error typically occurs when the VBA code tries to select a range that is not available, such as a cell that has been deleted, a range that has been shifted due to the insertion or deletion of cells, or the sheet that the code is trying to work on is not active. Typically, there are six types of Run Time Error 1004. They are:
- VBA Run Time Error 1004: That Name is already taken. Try a different One- This happens when you try to rename a Worksheet with a name that is already taken by another Worksheet of the Workbook.
- VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed- When you try to access a Named Range via VBA code but you somehow misspell the name of the Named Range then this error will show up.
- VBA Run Time Error 1004: Select Method of Range class failed- It occurs when users try to select a range from a Sheet that is not active or previously selected.
- VBA Runtime Error 1004 method open of object workbooks failed- occurs when the code attempts to open a workbook using the Workbooks.Open method and is unable to do so. This error can occur for a number of reasons, including:
-
- The workbook is already opened in another instance of Excel or by another user.
- The user does not have permission to access the file.
- The file is corrupted or damaged.
- VBA Runtime Error 1004 method Sorry We couldn’t Find:- This usually happens when we try to open a Workbook and the specified file path or name is incorrect or does not exist.
- VBA Runtime Error 1004: Application-Defined or Object-Defined Error when Selecting Range- This occurs when we try to select an object that does not exist.
Run Time Error 1004 Select Method of Range Class Failed: Possible Reason with Solutions
Today we will discuss the reason and then possible solutions to Run Time Error 1004: Select Method of Range Class Failed. This happens when a user tries to select a range from an inactive Sheet. The solution to that is very simple. The user just has to select or activate the Sheet before selecting the range.
Reason: Running Code While Worksheet Is Inactive
As the image below suggests, we are trying to select a range from the “Employee” Sheet which is not active, from the “Sales” Sheet, which is the active sheet. This results in an error for the VBA code.
If we debug the code, we will find that the Worksheets(“Employee”).Range(“B5:B12”).Select line is the source of error. Because the “Employee” Sheet is inactive. So the code cannot select the range from that Sheet.
Solution 1: Activating Sheet with Worksheet.Activate Method
In order to resolve the error, we need to activate the Sheet before selecting any range from it. So, we will make a modification to our current code. We will activate the Sheet first by using the Worksheet.Activate method and then select the range from the Sheet. Here is the corrected code.
Sub DotActivateMethod()
Worksheets("Employee").Activate
Worksheets("Employee").Range("B5:B12").Select
End Sub
The Worksheet.Activate method makes the current sheet or any specific sheet (if we assign any sheet) an active sheet. In this code, the Worksheets(“Employee”).Activate line activates the “Employee” Sheet before selecting the B5:B12 range from that Sheet.
As seen from the image below, the code works perfectly and selects the desired range.
Solution 2: Selecting Sheet with Worksheet.Select Method before Selecting Range
This is another way to fix the error. Here, we will select the Sheet with the Workhseet.Select method before selecting the range from that Sheet. Here is the modified code.
Sub DotSelectMethod()
Worksheets("Employee").Select
Worksheets("Employee").Range("B5:B12").Select
End Sub
The Workhseet.Select method allows us to select a specific worksheet in an Excel workbook. If we assign the Worksheet.Select method with any specific worksheet, the worksheet becomes the active sheet. Consequently, any commands will be applied to the worksheet. In the above code, The Worksheets(“Employee”).Select line simply selects the “Employee” Sheet before selecting the range from that Sheet.
As the image below illustrates, now the code works perfectly and does not show any errors.
How to Fix Run Time Error 1004: PasteSpecial Method of Range Class Failed Error in Excel
This is another common type of Run Time Error 1004. This usually happens when users fail to copy a range from a Sheet and paste it by using the PasteSpecial method using VBA code. Here, we are trying to paste some values in the B4:B12 range of the “Sales” Sheet. However, if we run the code, the code shows an error, as seen in the image below.
Now, if we debug the code, we will see that the code did not copy anything from any Sheet. So, the PasteSpecial method could not work properly.
So, we will make some changes to that code. We will copy some values from a range first and then paste them into our desired Sheet using the following code.
Sub PasteSpecialMethodError()
Worksheets("Employee").Range("B4:B12").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Here, the Worksheets(“Employee”).Range(“B4:B12”).Copy line copies the values from the B4:B12 range of the “Employee” Sheet. Besides, Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False – this line pastes only the values of the copied range in the cells taken from the user selection.
As seen from the image below, the code works perfectly and the copied range is pasted properly into the selected range.
How to Fix VBA Runtime Error 1004: Application-Defined or Object-Defined Error When Selecting Range
In this instance, we will talk about the VBA Runtime Error 1004: Application-Defined or Object-Defined Error when selecting range. This happens when a user tries to select an object that does not exist or is invalid. As the below image shows, when we try to copy a range from a Sheet and paste it into another Sheet the VBA code shows an error. Because the Named Range we are trying to copy does not exist.
If we debug the code, we can clearly see that the “EmployeeName” Named Range does not exist and that is why the VBA editor is showing an error in that line.
In order to resolve the issue, we need to create a Named Range called “EmployeeName”. Follow the steps below to do so,
- Go to Formulas >> Define Name.
- A prompt will appear on the screen.
- In the prompt, first type “EmployeeName” inside the Name box.
- Next, select B4:B12 as the range for this Named Range.
- Finally, click OK.
- As a result, the B4:B12 range of the “Employee” Sheet will be termed as “EmployeeName”.
Now, if we run the code, it will show no errors.
Conclusion
In this article, we have talked about Run Time Error 1004: Select Method of Range Class Failed. We tried to find out the reason behind the error and gave some possible solutions. This article will allow users to familiarize themselves with the Run Time Error 1004, which is one of the most commonly encountered errors in VBA. It will also help them to find a solution to this error and make their code more effective. Also, if you want to see more Excel content like this, please visit our website, Exceldemy, and unlock a great resource for Excel-related content.
