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

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

--
Regards,
Tom Ogilvy


"snax500" wrote in message
oups.com...
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

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

--
steveB

Remove "AYN" from email to respond
"snax500" wrote in message
oups.com...
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