short cut keys work- run macro does not

[snax500]

In Excel2000, I have the following macro that I use with a short cut
key:

Sub Copy_Values()
'
'
' Keyboard Shortcut: Ctrl+Shift+Z
'

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

I use this to Paste-Values after I have copied something. It works if I
use the assigned short cut keys (Ctrl+Shift+Z) but if I copy and then
try to run the macro using the Visual Basic toolbar, I get an error
message (PasteSpecial of Method of Range Class Failed). How come it
works with Short Cut keys but not with the Run icon?

Thanks

 

[Tom Ogilvy]

I suspect that running from the menu causes the clipboard to be cleared. At
least that has been my experience.

 

[STEVE BELL]

Here's a snippet that I use to paste formula, see if changing it to
paste value will work. Caution - it only works on the active sheet.

Dim rng

Set rng = Application.InputBox("Select Cell", Type:=8)

Selection.Copy
rng.Select
Selection.PasteSpecial Paste:=xlFormulas