Macro for "Paste Special – Values"

[ar]

I do a lot of pasting data into Excel using the “Paste Special – Values” function. Having a macro/shortcut keys for this function will be faster than going to Edit –Paste Special – Values all the time. I tried using the Macro Recorder but that didn’t work. Could someone provide the macro code for this function? Note that I’m not familiar with macros so please provide step-by-step instructions. Thanks.

[Russell Hauf]

No macro code needed. Go to Tools-Customize. A dialog will appear. Click on the Commands tab. Under Categories, select Edit. Then scroll down on the right side until you see Paste Values (not Paste Special - it's below Paste Special). Now drag this button up to your toolbar - put it anywhere you like (a logical place is next to the regular Paste toolbar button).

I have this on my toolbar and use it all the time.

Hope this helps,

Russell

[Asala42]

That's pretty cool, Russ,

You know, I don't think I *ever* even thought about hitting Tools/Customize

Still, since I'm still a VBA beginner would someone post a VBA answer anyway? I also tried starting with the macro recorder, tried tweaking a bit and fell pretty much on my face. I'll probably also assign a Ctrl+key to it since I try to avoid using the mouse.

Thanks,
Adam

[NateO]

Hello Adam, a few examples below of copying a1's value to b1 (as a value):

Sub pstsPec1()

[a1].Copy

[b1].PasteSpecial Paste:=xlValues

Application.CutCopyMode = False

End Sub



Sub pstsPec2()

[b1].Value = [a1].Value

End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-23 10:54 ]

[Rocky E]

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

is the from the macro recorder. I normally bind it to a key.


_________________
Hope this helps!

Rocky

"Be not the first by whom the New are try'd,
Nor yet the last to lay the Old aside."
Alexander Pope (1688-1744).

[ This Message was edited by: Rocky E on 2002-05-23 11:04 ]

[Asala42]

Hmm..

Ok - I used that code earlier and get a Runtime error (1004). But for some reason it works fine when I assign it a shortcut key (as opposed to going to Alt+F8/select macro/run). *shrugs* Oh well.

Thanks all!!
Adam

[ This Message was edited by: Asala42 on 2002-05-23 11:57 ]

[Joe Was]

This code gets copied to a Module. Right click: Sheet Tab, View code, From Menue "Insert, Module" (If a module is not displayed, if you record a macro a module is automatically added.) then paste the code below, into the module (make certain you dubble click the module to open the code sheet!) The code below has two PasteSpecial commands, so you can see how to stack options if needed. If you do not need other options delete the "format" paste from the code below. JSW

Sub sPaste()
'This code pastes the format and value of the current selection,
'to the active range. The option has been set to Hot-key Ctrl-s

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

[Joe Was]

Note: Clipboard code will sometimes fail if you run your code from the tool menu. The menu activation will over-ride the loaded copy. So set your Sub's to a macro hot-key under macro-macros-options or use a form button to call the macros.

The code below can be used as a companion to the code above. This code will load the current selection on to the clipboard so the above code will have something to paste! JSW

Sub myCopy()
'Copy current selection to the clipboard.
ActiveCell.Select
Selection.Copy
End Sub