Caution Writing PasteSpecial Macros

PasteSpecial Error in Excel with VBA Macro

What's The Story?

One day I was teaching a co-worker how to create simple macros to use across any of his workbooks.  I like to refer to these kinds of procedures as Personal Macros.  We were messing around with the PasteSpecial functionality by trying to Paste Values Only and Paste Column Widths simultaneously.  The simple code looked something like below.

Sub PasteSpecial_Values_Width()
  
  Selection.PasteSpecial Paste:=xlPasteColumnWidths
  Selection.PasteSpecial Paste:=xlPasteValues

End Sub

The problem was that when we ran the macro, we kept getting an error message that wasn’t making any sense to me:

After spending days trying to figure out why this was occurring, I finally was able to solve my mystery! 

What Was Causing This Error?

In order to run the macro, I first needed to copy a range of cells.  This puts the ‘marching ants’ around the selected range, notifying that the content within that range is now posted to the clipboard.  I then would select the cell(s) I wanted to paste the copied data to.  Once I made my selection I went to the Developer tab in the Ribbon, clicked the Macro button (alternatively you can hit Alt + F8), and then executed my PasteSpecial_Values_Width macro.  At this point I received the nasty error dialog box that made no sense to me (pictured above).

So why was this happening?  Well, I figured out that when you access the Macro Dialog Box (through the Developer tab or hitting Alt +F8) Excel decides to clear the clipboard.  In fact, it appears if you access any dialog box in Excel, the marching ants will disappear!  This was in turn confusing my macro because there is nothing on the clipboard to paste!

Lesson Learned

So the reason I am posting this story is to hopefully bring awareness to the fact that if you are creating a pasting macro that relies on data being copied prior to execution, you must either run it by

  • Assigning the macro to an object (creating a button)
  • Assigning a shortcut key to run the  macro code
  • Running the macro in the VBE editor (shortcut key F5)

Running your macro via the Macro Dialog Box will cause you an error every single time.  Make sure you put comments in your code that explain this so your users do not get confused like I did.  Better yet, add an error handler that gives the user a message box telling them exactly how to run the macro.  Below is an example of the code I ended up writing to accomplish my task at hand.

Sub PasteSpecial_Values_Width()

On Error GoTo NothingCopied

  Selection.PasteSpecial Paste:=xlPasteColumnWidths
  Selection.PasteSpecial Paste:=xlPasteValues

On Error GoTo 0

Exit Sub

NothingCopied:
  MsgBox "There is no data on the clipboard to be pasted." & vbNewLine & vbNewLine _
    & "NOTE: Clipboard is automatically cleared when running macros through the " _
    & "Macro Dialog Box (Alt + F8).  Run macro by assigning it to an object, " _
    & "creating a key shortcut, or in the VB Editor (shortcut F5)."

End Sub

As an alternative to the code above, you can also test to see if there is any data currently stored on the clipboard.  I like this method much better because it stays away from messing with the VBA Error Handler.  Much thanks to Brian Hartman for pointing this out in the comments section!  Here is what he suggested:

Sub PasteSpecial_Values_Width()

If Application.CutCopyMode = True Then
  
  Selection.PasteSpecial Paste:=xlPasteColumnWidths
  Selection.PasteSpecial Paste:=xlPasteValues

Else
  
  MsgBox "There is no data on the clipboard to be pasted." & vbNewLine & vbNewLine _
   & "NOTE: Clipboard is automatically cleared when running macros through the " _
   & "Macro Dialog Box (Alt + F8).  Run macro by assigning it to an object, " _
   & "creating a key shortcut, or in the VB Editor (shortcut F5)."

End If

End Sub

Video Tutorial (For you Visual Learners!)

Have you been getting weird or unexplained error messages why using PasteSpecial in your VBA code? Check out what I discovered while searching for the answers!

How About You?

Have you ever experienced this problem or anything similar in nature? Let me know in the comments section below!