PasteSpecial Method of Worksheet Class Failed

Get FREE Advanced Excel Exercises with 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.


What Is PasteSpecial Method of Worksheet Class Failed Error in Excel?

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 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 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 for 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:

PasteSpecial Method of Worksheet Class Failed

As you can see, there is a runtime error. It shows that showing PasteSpecial method of Worksheet class failed.


PasteSpecial Method of Worksheet Class Failed Error: 3 Possible Reasons & Solutions

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:

expression.PasteSpecial (Format, Link, DisplayAsIcon, IconFileName, IconIndex, IconLabel, NoHTMLFormatting)

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

Format Argument String Value
0 “Picture (PNG)”
1  “Picture (JPEG)”
2  “Picture (GIF)”
3 “Picture (Enhanced Metafile)”
4 “Bitmap”
5 “Microsoft Office Drawing Object”

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.


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.


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.


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


Download Practice Workbook


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.

Keep learning new methods and keep growing!


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo