short cut keys work- run macro does not

S

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
 
T

Tom Ogilvy

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top