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

VBA Code with the PasteSpecial Method of Range Class Failed Error

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.

PasteSpecial Method of Range Class Failed Error in VBA

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

PasteSpecial Method of Range Class Failed Error Solved


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

VBA Code with the PasteSpecial Method of Range Class Failed Error

Running this code will result in the run-time error 1004.PasteSpecial Method of Range Class Failed Error in VBA

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

PasteSpecial Method of Range Class Failed Error in VBA

⧭ 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

Solved Code of the PasteSpecial Method of Range Class Failed Error

PasteSpecial Method of Range Class Failed Error Solved

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

VBA Code with the PasteSpecial Method of Range Class Failed Error

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.

PasteSpecial Method of Range Class Failed Error in VBA

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

PasteSpecial Method of Range Class Failed Error Solved

Read More: PasteSpecial Method of Worksheet Class Failed


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo