Please help with PasteSpecial programming

[Peter]

Hi,

I have the following code working fine:

Sub Macro(1)
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

The problem with this code is the destination cell is hardcoded. I'd
like to be able to select any cell, copy it, select any other cell
(using the mouse to navigate) and PasteSpecial into that cell.

When I manually select a cell, copy and run:

Sub Macro(2)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

by itself, I get the following error message:

Run-time error '1004':

PasteSpecial method of Range class failed.

How do I get Macro(1) to work?

Thanks,

Peter

 

[Don Guillett]

Then wouldn't it be easier to do just that using the built in copy button
and the built in paste button. You may have to add the paste button by
customizing your toolbar. It's the one that has a little 12 in the box in
the edit.

 

[Bill Renaud]

How do I get Macro(1) to work?
Do you mean Macro(2)?

Try this (Excel sometimes requires declaring and setting a reference):

Sub Macro(2)
dim rng as Range
set rng = ActiveCell

rng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:= False, Transpose:=False
End Sub

 

[Peter]

Thanks for your reply. Yes, I found the Paste Value button. Is there a
way to get that to appear in the pop-up menu when I right-click a
cell?

Peter