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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,501
Messages
5,832,079
Members
430,110
Latest member
Chyke_mxl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top