sendkeys and paste-special

[Tim Childs]

Hi

I want to use Sendkeys (never recommended, I know) to emulate the keystrokes
Alt-Edit Paste-Special Values

I tried this sort of thing but end up with "v" in the active cell in Excel
when running it

Pl can someone help

Thanks

Tim

Sub A__Paste_Value()

SendKeys "%esv{TAB 4}~", True '',
End Sub

 

[Frank Kabel]

Hi Tim
why aren't you the PasteSpecial method of a range/worksheet (have a
look at the Excel help for this method). No need for SendKeys IMHO

 

[Ron de Bruin]

Hi Tim

Can you explain why you want to use this Tim?

You can add a button on your toolbar if you want

View>ToolBars>customize
click on the commands tab
choose Edit
Drag the Paste Values button on that list to a Toolbar

 

[Tim Childs]

Ron and Frank

Thanks for that tip - very useful. I really wanted to get the functionality
linked to key strokes e.g. a macro with short-cut letters Ctrl-Shift-V but
maybe that is not available? I like the sendkeys as if all is working as
intended the Undo facility still works unlike when "real macros" are
operated.

Best wishes

Tim

 

[Jim Rech]

I don't think this will work too well, Tim. You're trying to pump
keystrokes through the keyboard at the same time as it's in use because
you're using a shortcut. You might have to forgo undo and use Frank's
suggestion. How often do you make a mistake? Practically never!

 

[David McRitchie]

Hi Tim,
If you want a macro that you can assign a short cut key of Ctrl+Shift+V
to, see the first example in
Paste / Pasting
http://www.mvps.org/dmcritchie/excel/paste.htm

 

[Tim Childs]

Jim

Can you explain a bit more: surely by the time I have finished the
ctrl-shift-V the keyboard is not in use

I agree I have been unable to make it work, tho' !

Thanks

Tim

 

[Tim Childs]

David

Thanks for this link to the workaround (it does not undo AFAIK) - maybe I
make more mishhhtakes than Jim thinks!!

Tim

Hi Tim,
If you want a macro that you can assign a short cut key of Ctrl+Shift+V
to, see the first example in
Paste / Pasting
http://www.mvps.org/dmcritchie/excel/paste.htm

 

[Tim Childs]

David

Thanks for this - have now incorporated it into my personal workbook on the
basis it fulfills 99% of my requirements

hope I did not sound ungrateful earlier

THANKS

Tim

Hi Tim,
If you want a macro that you can assign a short cut key of Ctrl+Shift+V
to, see the first example in
Paste / Pasting
http://www.mvps.org/dmcritchie/excel/paste.htm

 

[David McRitchie]

Hi Tim,

hope I did not sound ungrateful earlier

Not at all, not being able to do an undo is important, and I did not realize
that that was not mentioned on my pages for installing a macro so I added
a comment about not being able to undo macro to them as well as to the
paste.htm page, where special mention is important because the
shortcut is similar to the builtin in paste (ctrl+v) builtin keyboard shortcut
which can be undone. Your feedback is important and would have made
me make the changes, but it was actually Jim's comment that caused me
to change it before I read your reply.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm