PasteSpecial Method of Worksheet Class Failed (Reasons & Solutions)

While working with Excel VBA, you will face a lot of runtime errors. It is very usual to get these problems. Over time, you will get more promising at bypassing these errors, but it is doubtful you will ever write flawless code the first time. But, if you learn how to avoid, track down, and deal with these errors, you will save yourself a lot of time in the future. in this tutorial, you will learn to fix the problem of PasteSpecial Method of Worksheet Class Failed. So, stay with us.

Download Practice Workbook

What is the “PasteSpecial Method of Worksheet Class Failed” Error?

You can copy and paste using the VBA macro in various ways. The paste special method is one of them.

Now, the PasteSpecial method of Worksheet class failed is a run time error. Your code is generally valid in regulation, but the action you took or the data existing utilized leads to unwanted errors.

After fixing syntax or compile errors, we try to run our Excel VBA code to see how well it is executing. We observe the code based on our desired actions and calculations. We call this live execution of code “runtime”.

You cannot detect the runtime errors by simply peeking at the code. These errors are the outcomes of our code interacting with particular data or inputs at the current time.

There are several reasons to cause these errors. Unexpected data passed to the VBA code, mismatching data types, dividing by random zeros, and specified cell ranges not being available. Runtime errors are also one of the most assorted and complicated to track down and resolve.

Take a look at the following VBA code:

Sub copy_picture()
Dim picture_name As Picture

For Each picture_name In ActiveSheet.Pictures
       If picture_name.Name = "Picture 4" Then
              picture_name.Left = ActiveCell.Left
              picture_name.Top = ActiveCell.Top
       End If
       If picture_name.Left = ActiveCell.Left And picture_name.Top = ActiveCell.Top Then
              picture_name.Copy
              Sheets("Sheet2").Select
             ActiveSheet.PasteSpecial Format:=xlPicture
       End If
Next picture_name

End Sub

We wrote this code to copy a picture from a sheet and paste it on another sheet. But whenever we try to run the code, Excel shows us the following error:

As you can see, there is a runtime error. And it is showing PasteSpecial method of Worksheet class failed.

Possible Reasons and Solutions of  “PasteSpecial Method of Worksheet Class Failed” Error

Now, before we start, let’s clarify it first. Excel VBA codes vary from person to person. The problem you faced might not be the same one we showed earlier. But, there may be some reasons you can see and try to fix them. In the upcoming sections, we are providing you with some reasons to fix the PasteSpecial method of the Worksheet class failed. I recommend you learn and apply these methods to your worksheet.

1. PasteSpecial Method of Worksheet Class is Not Valid

One of the main reasons that you can have is declaring invalid PasteSpecial formats of worksheet class. In the previous code, you saw we used “Formats” after the PasteSpecial command.

ActiveSheet.PasteSpecial Format:=xlPicture

Now, this format is invalid.

The Generic Syntax:

Now, in the format argument, values you can use are the following:

These are the valid formats for “Format:=”. But, we used a different format in the code. So, this might cause an error.

Now, there are also various formats of worksheet objects that you can use in the workbook. We showed just one of them. Try to select them.

Read More: Run Time Error 1004: PasteSpecial Method of Range Class Failed

2. PasteSpecial Method of Worksheet Class Failed Because There is Nothing in Clipboard

Now, when you run the ActiveSheet.PasteSpecial command, Excel VBA macro supposes that the Clipboard has some content that you already copied to it.

But, the macro will generate a PasteSpecial method worksheet class failed error if you haven’t copied anything. So, technically you are pasting nothing.

To verify whether you have copied something or not, select any cell. After that, press Ctrl+V on your keyboard. If you have copied something, you will see the content there.

Now, if there is nothing, there will be a blunder when you run the VBA macro command to do the same.

So, again check your VBA macro to see your copy statement.

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

3. Destination Worksheet is Not Selected

Another thing is sometimes the PasteSpecial method only works for a range of cells or selection. In those cases, you can’t use this method.

Another reason you can find is you have to select your destination worksheet object in your VBA macro. If your destination worksheet doesn’t exist, the code might return false for the paste special method.

As we mentioned earlier, it is a runtime error. You can not identify it with open eyes. You will have to deal with it after executing the code.

Read More: Difference Between Paste and Paste Special in Excel

💬 Things to Remember

✎ If you are using the xlApp variable, don’t use early binding to the dimension of this. Use late binding to initialize that variable.

✎ The basic reason for the pastespecial method of worksheet class failed error is using wrong arguments in the .pastespecial method.

✎ Make sure your workbook is protected or not. If your sheet is private, it may cause an error.

✎ You can implement DoEvents in your code.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to solve the pastespecial method of worksheet class failed error. I know this tutorial didn’t show deeply. But, I tried to provide you with some basic reasons that may appear.

We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• VBA Code to Compare Two Excel Sheets and Copy Differences
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
• How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
• VBA Paste Special to Copy Values and Formats in Excel (9 Examples)
• How to Copy Alternate Rows in Excel (4 Ways)
• Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
• Excel Formula to Copy Cell Value from Another Sheet