Run Time Error 1004: Select Method of Range Class Failed

How to Launch the VBA Macro Editor in Excel

Go to Developer Tab >> Visual Basic.
The Visual Basic window is displayed.

Opening Visual Basic Window

Select Insert >> Module.

Inserting VBA Module

Enter the code.
Click Run or F5 key to run the code.

What Exactly Is Run Time Error 1004?

Run Time Error 1004 occurs when the VBA code tries to select a range that is not available. There are six types of Run Time Error 1004:

VBA Run Time Error 1004: That Name is already taken. Try a different One- When you try to rename a Worksheet with a name that is already taken by another Worksheet in 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 misspell the name of the Named Range.
VBA Run Time Error 1004: Select Method of Range class failed- When you try to select a range from a Sheet that is not active or was previously selected.
VBA Runtime Error 1004 method open of object workbooks failed- When the code tries to open a workbook using the Workbooks.Open method and is unable to do so. This error can occur because:

1. The workbook is open in another instance of Excel or by another user.
2. The user does not have permission to access the file.
3. The file is corrupted or damaged.

VBA Runtime Error 1004 method Sorry We couldn’t Find:- 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- When we try to select an object that does not exist.

Reason – Running the Code When the Worksheet Is Inactive

In the image below the “Sales” Sheet is active. While trying to select a range from the “Employee” Sheet, which is not active, an error occurs.

If you debug the code, you will find that the line Worksheets(“Employee”).Range(“B5:B12”).Select is the source of error. The “Employee” Sheet is inactive and the code cannot select the range.

Solution 1 – Activate Sheet using the Worksheet.Activate Method

Activate the Sheet using the Worksheet.Activate method and then select the range.

This 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 assigned sheet an active sheet. In the code, the Worksheets(“Employee”).Activate line activates the “Employee” Sheet before selecting B5:B12.

This is the output.

Solution 2 – Select the Sheet using the Worksheet.Select Method before Selecting the Range

Select the Sheet with the Workhseet.Select method before selecting the range. This is the modified code.

Sub DotSelectMethod()
Worksheets("Employee").Select
Worksheets("Employee").Range("B5:B12").Select
End Sub

If you assign the Worksheet.Select method to any specific worksheet, it becomes the active sheet.

In the above code, Worksheets(“Employee”).Select selects the “Employee” Sheet before selecting the range.

This is the output.

How to Fix the Run Time Error 1004: PasteSpecial Method of Range Class Failed

This error fail usually happens when you copy a range from a Sheet and paste it using the PasteSpecial method in a VBA code.

While trying to paste values in B4:B12, the code shows an error:

If you debug the code, you will see that the PasteSpecial method did not work.

Change the code:

Sub PasteSpecialMethodError()
Worksheets("Employee").Range("B4:B12").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Worksheets(“Employee”).Range(“B4:B12”).Copy copies the values in B4:B12 of the “Employee” Sheet. Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False – pastes the values of the copied range only in the selected cells.

This is the output.