Caution Writing PasteSpecial Macros
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 the following:
Sub PasteSpecial_Values_Width()
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Selection.PasteSpecial Paste:=xlPasteValues
End SubThe 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 SubAs 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 SubVideo Tutorial (For you Visual Learners!)
How About You?
Have you ever experienced this problem or anything similar in nature? Let me know in the comments section below!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.