Run Time Error 1004: PasteSpecial Method of Range Class Failed

One of the most common problems that we encounter while working with copying and pasting in VBA is the run time error 1004: PasteSpecial Method of Range Class Failed. In this article, I’ll show you what the possible reasons are behind this error and how to solve those, with proper examples and illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


PasteSpecial Method of Range Class Failed: Causes and Solutions

Without further delay, let’s go to our main discussion. That is, what may be the possible reasons behind this error, and how to solve those.


Reason 1: Accessing PasteSpecial Method without Copying Anything

This is the most common reason behind the error. That is, trying to access the PasteSpecial method without copying anything.

In order to understand it clearly, check the following VBA code.
⧭ 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

Here, we’ve used the PasteSpecial method of VBA without copying anything. So Excel will display a run-time error 1004 when you run it.

PasteSpecial Method of Range Class Failed Error in VBA

⧭ Solution:

To solve this problem, first, you need to copy a range of cells then access the PasteSpecial method.

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 code, it’ll paste the formulas of the range B3:B5 of the active worksheet to the selected range.

PasteSpecial Method of Range Class Failed Error Solved

Read More: PasteSpecial Method of Worksheet Class Failed (Reasons & Solutions)


Reason 2: Accessing PasteSpecial Method with Spelling Error

This is another common reason behind the error. That is, to access the PasteSpecial method with spelling error(s) in any argument.

Look at the following VBA code to make it clear. Here 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

When you run this code, you’ll get 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.

Read More: How to Use Paste Special Command in Excel (5 Suitable Ways)


Similar Readings


Reason 3: Opening a New Workbook After Copying that Cancels the Copy/Paste Mode

This is another important reason behind the error. That is, to do something that cancels the copy/paste mode before pasting.

Look at the following code for understanding clearly.

⧭ 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

Here we have copied the range B3:B5 from the Sheet1 of a workbook called Workbook1.

Then we’ve created a new workbook called Workbook2 in the same folder and tried to paste the copied range to the range B3:B5 of Sheet1 of that workbook.

But when we run the code, it’ll display a PasteSpecial Method of Range Class Failed Error, because the moment we create the new workbook, the copy/paste mode will be canceled.

PasteSpecial Method of Range Class Failed Error in VBA

⧭ Solution:

To solve this problem, first, write down the lines of code to create the new workbook called Workbook2.

Then insert the lines to activate Workbook1 and copy the desired range from it.

And 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

Run this code. It’ll copy the range B3:B5 from Sheet1 of Workbook1.

PasteSpecial Method of Range Class Failed Error Solved

And paste it into Sheet1 of the newly created workbook called Workbook2.

⧭ Precaution:

Obviously, don’t forget to keep Workbook1 open while running the code.

Read More: How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)


Reason 4: Turning Application.CutCopyMode to False that Cancels the Copy/Paste Mode

Finally, there may be another reason for the error to happen. We may turn off the Application.CutCopyMode mistakenly before accessing the PasteSpecial method.

Though it’s not a very common practice, still sometimes we make it when we are to work with a long number of lines.

See the following code for understanding it clearly. Here we’ve copied the range B3:B5, but canceled the CutCopyMode before pasting it.

⧭ 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

When you’ll run the code, it’ll show the PasteSpecial Method of Range Class Failed error.

PasteSpecial Method of Range Class Failed Error in VBA

⧭ Solution:

I guess by now you all have guessed the solution. It’s quite simple actually. Just remove the line from the code that turns the CutCopy mode off.

So, the correct VBA code will be:

Sub PasteSpecial_Method_of_Range_Class_Failed()

Range("B3:B5").Copy

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

End Sub

It’ll copy the range B3:B5 and paste it over the D3:D5 without any trouble.

PasteSpecial Method of Range Class Failed Error Solved

Read More: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)


Things to Remember

Here I’ve shown only the troubles that you may encounter while working with the PasteSpecial method in VBA. If you want to know the PasteSpecial method in detail, visit this link.


Conclusion

So, in short, these are the reasons that may cause the run-time error 1004: PasteSpecial Method od Range Class Failed in your code. I hope you’ve understood all the points clearly and those will help you a lot in the future. Do you know any other reasons? And do you have any problems? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo