One of the most common problems that we encounter while working with copying and pasting in Excel 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.
PasteSpecial Method of Range Class Failed: 4 Reasons 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
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.
⧠Solution:
To solve this problem, first, you need to copy a range of cells and 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.
Read More: PasteSpecial Method of Worksheet Class Failed
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
When you run this code, you’ll get 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 New Workbook After Copying that Cancels 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 to understand it 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 Sheet1 of a workbook called Workbook1.
Then we 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.
⧠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
Run this code. It’ll copy the range B3:B5 from Sheet1 of Workbook1.
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.
Reason 4: Turning Application.CutCopyMode to False Cancels 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, sometimes we make it when we are to work with a long number of lines.
See the following code to understand 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
When you run the code, it’ll show the PasteSpecial Method of Range Class Failed error.
⧠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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
So, in short, these are the reasons that may cause the run-time error 1004: PasteSpecial Method of 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 in the below comment box.
