selection.PasteSpecial gave me an error :(

[Phil11]

I want to write a one-line macro to "paste special." It's supposed to paste the Excel clipboard into whatever cell is currently selected.

Here it is.

Sub macro1()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub


It fails every time: "PasteSpecial method of range class failed."

Is this because I didn't use a ______.copy statement in the code just before this? The data WAS ALREADY IN the clipboard. How do I access that data and just paste it?

Thanks so much,
-Phil

 

[Andrew Poulsom]

What code do you get if you record a macro while doing the Paste Special manually?

Both the Range object and the Worksheet object have a PasteSpecial method, but the arguments are different. From Help:

PasteSpecial Method (Range Object)

Pastes a Range from the Clipboard into the specified range.

Syntax

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

PasteSpecial Method (Worksheet Object)

Pastes the contents of the Clipboard onto the sheet, using a specified format. Use this method to paste data from other applications or to paste data in a specific format.

Syntax

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

 

[Phil11]

OK - I figured it out.

You know how Excel forgets what's in the clipboard the second you do something, like press a key or choose a menu option?

Well, running the macro (by going Tools-->Macros) EMPTIED THE CLIPBOARD, so then the PasteSpecial command failed.

But, if I assign a hotkey to the macro, then Excel does allow me to press that hotkey and executes the macro successfully.

-P