selection.PasteSpecial gave me an error :(

Phil11

New Member
Joined
Jul 8, 2005
Messages
48
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 8, 2005
Messages
48
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,497
Members
412,670
Latest member
Khin Zaw Htwe
Top