Run Time Error 1004 – PasteSpecial Method of Range Class Failed (4 Reasons and Solutions)

Reason 1 – Accessing PasteSpecial Method without Copying Anything

The most common reason for this error is attempting to use the PasteSpecial method without first copying anything. In other words, you’re trying to apply PasteSpecial without having previously copied any data.

Consider the following VBA code snippet:

⧭ VBA Code

Sub PasteSpecial_Method_of_Range_Class_Failed()
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

In this code, the PasteSpecial method is used without any prior copy action. As a result, Excel will display a run-time error 1004 when you run this code.

⧭ Solution

To resolve this issue, ensure that you copy a range of cells before attempting to use the PasteSpecial method. For example:

Sub PasteSpecial_Method_of_Range_Class_Failed()
Application.Range("B3:B5").Copy
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

When you run this modified code, it will paste the formulas from the range B3:B5 of the active worksheet to the selected range.

Reason 2 – Accessing PasteSpecial Method with Spelling Error

Another common cause of the error is using the PasteSpecial method with spelling errors in its arguments. Let’s look at the following VBA code snippet where we’ve made a spelling mistake in the argument xlPasteAll.

⧭ VBA Code

Sub PasteSpecial_Method_of_Range_Class_Failed()

Application.Range("B3:B5").Copy

Selection.PasteSpecial Paste:=xlPaseAll

End Sub

Running this code will result in the run-time error 1004.

⧭ Solution

The solution is easy. I’m sure you already have guessed that. Just make sure that the spellings of all the arguments have been made correctly.

And the error will automatically vanish.

Reason 3 – Opening a New Workbook After Copying Cancels Copy/Paste Mode

Another important reason for encountering this error is attempting to perform an action that cancels the copy/paste mode before pasting. Let’s examine the following code snippet:

⧭ VBA Code

Sub PasteSpecial_Method_of_Range_Class_Failed()

Workbooks("Workbook1.xlsx").Worksheets("Sheet1").Range("B3:B5").Select
Selection.Copy

Dim Workbook2 As Workbook
Set Workbook2 = Workbooks.Add

Workbook2.SaveAs Filename:=ThisWorkbook.Path & "\" & "Workbook2.xlsx"
Workbook2.Activate
Workbook2.Unprotect

Workbook2.Worksheets("Sheet1").Range("B3:B5").Select
Selection.PasteSpecial Paste:=xlPasteAll

End Sub

In this code:

1. We copy the range B3:B5 from Sheet1 of a workbook named Workbook1.
2. Next, we create a new workbook called Workbook2 in the same folder.
3. We attempt to paste the copied range into the B3:B5 range of Sheet1 in Workbook2.

However, when we run this code, it displays a PasteSpecial Method of Range Class Failed error. This occurs because the moment we create the new workbook (Workbook2), the copy/paste mode is canceled.

⧭ Solution

To resolve this issue:

1. First, write the lines of code to create the new workbook (Workbook2).
2. Then insert the lines to activate Workbook1 and copy the desired range from it.
3. Finally, activate Workbook2 and paste the copied range there:

Sub PasteSpecial_Method_of_Range_Class_Failed()

Dim Workbook2 As Workbook
Set Workbook2 = Workbooks.Add

Workbook2.SaveAs Filename:=ThisWorkbook.Path & "\" & "Workbook2.xlsx"
Workbooks("Workbook1.xlsx").Activate

Worksheets("Sheet1").Range("B3:B5").Select
Selection.Copy

Workbook2.Activate
Worksheets("Sheet1").Range("B3:B5").Select
Selection.PasteSpecial Paste:=xlPasteAll

End Sub

Paste it into Sheet1 of the newly created workbook called Workbook2.

⧭ Precaution

Don’t forget to keep Workbook1 open while running the code.

Reason 4 – Turning Application.CutCopyMode to False Cancels Copy/Paste Mode

There’s another reason why this error might occur. Sometimes we inadvertently turn off the Application.CutCopyMode before accessing the PasteSpecial method.

Although it’s not a very common practice, it can happen, especially when working with a large number of lines of code.

Consider the following VBA code snippet:

⧭ VBA Code

Sub PasteSpecial_Method_of_Range_Class_Failed()

Range("B3:B5").Copy

Application.CutCopyMode = False

Range("D3:D5").PasteSpecial Paste:=xlPasteAll

End Sub

In this code:

1. We copy the range B3:B5.
2. Then, mistakenly, we cancel the CutCopyMode before attempting to paste it into the D3:D5 range.

When you run this code, it will display the PasteSpecial Method of Range Class Failed error.

⧭ Solution

The solution is straightforward. Simply remove the line that turns off the CutCopyMode:

Sub PasteSpecial_Method_of_Range_Class_Failed()

Range("B3:B5").Copy

Range("D3:D5").PasteSpecial Paste:=xlPasteAll

End Sub

This corrected code will copy the range B3:B5 and paste it into D3:D5 without any trouble.

Read More: PasteSpecial Method of Worksheet Class Failed

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Difference Between Paste and Paste Special
• [Fixed!] Paste Special Not Working

<< Go Back to Paste Special | Paste | Copy Paste in Excel | Learn Excel