Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

in excel - command: paste special

Status
Not open for further replies.

kenguru

Programmer
May 14, 2004
173
RO

I would like to make a shotcut key for pasting a text only their value. I tryed making a macro, and using no reference, but no luck. When I tryed to use it it craks.

Any idea how to make this possible, or exist already a shortcut key for this?

Thank You,
Kenguru
 
Why make a shortcut when there already is one?

Alt + E, S, V

That is press Alt + E together, then press S, then press V, then press Enter.

-----------
Regards,
Zack Barresse
 
You are right, exist that shortcut, but that is a bit slow, and takes a lot of time, if i have to make it over-and-over again.

Kenguru.
 
You could have posted your code example and we'd have been able to tell you why it "cracks".

Not sure exactly what you meant but...
Code:
Sub PasteTextValues()
' Keyboard Shortcut: Ctrl+t
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

or
Code:
Sub ReplaceContentWithTextValue()
' Keyboard Shortcut: Ctrl+t
    Selection.Furmula = Selection.Value
End Sub
In Excel XP and 2003 there is even a toolbar button you can add...(customise your toolbar>Commands>edit>pastevalues)

Sean Murphy
Call Centre Analyst
IAG New Zealand
 
:really must proof-read better:
Selection.Furmula = Selection.Value
should be
Selection.Formula = Selection.Value

Sean Murphy
Call Centre Analyst
IAG New Zealand
 
A bit slow? I think you're being a little over-bearing. It only takes me roughly a second to do. Trust me, you get used to it and you'll never beat it's functionality speed with code. Not to mention not having the ability to undo it as you would using the native feature.

-----------
Regards,
Zack Barresse
 
Hi Kenguru

You could use code like:
Code:
Sub PasteSpecialFormulas()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlFormulas
End Sub
and attaching to a keyboard shortcut and/or a custom menu item. After doing that, you can prefix the sub with 'Private' to stop it showing in the macro list.

The 'On Error Resume Next' line is there just in case you try to paste when there are no textual data on the clipboard.

Cheers

[MS MVP - Word]
 
Oops, wrong sub.

More importantly, though, if you want to paste values only, why not just add the PasteSpecial Values button to your toolbar? You'll find it under View|Toolbars|Customize|Commands|Edit.
There's another one there for pasting formats too.

Cheers


[MS MVP - Word]
 
Zack - to be fair - I have that macro set up and set to Ctrl+z (I don't mind using buttons for undo) so that I can do very quick copy/paste values (Ctrl+C, Ctrl+z)

lazy - but hey - that's what VBA is for !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Ctrl + z ?!?! Oh goodness, I can barely look. ;)

-----------
Regards,
Zack Barresse
 
Ctrl-z is a good option to, I put ctrl-a :)
z is quite 'near' to CTRL :D

Kenguru
 
...I like CTRL+a for selecting everything in the current region....all my cut / copy / paste options are along the bottom row: z,x,c,v

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
I always *highly* recommend you not use a shortcut which is used natively by Excel. Ctrl + Z is undo, Ctrl + A is select all. There are bunches. There are a lot of Ctrl + Shift shortcuts available though, i.e. Ctrl + Shift + Z. ;)

-----------
Regards,
Zack Barresse
 
Have to agree with Zack. Overwriting a built-in shortcut such as Ctrl+a, Ctrl+z in the Personal Macro workbook is not a good idea. Having it available in a specific workbook/VBapplication is a little more acceptable.

Sean Murphy
Call Centre Analyst
IAG New Zealand
 
Zack / Sean - why ? I am not trying to be difficult - I just don't see any reason why not to overwrite a built in shortcut.

I rarely use undo - normally I will just delete and re-enter but if and when I do, I tend to use the toolbar button to do so. What problems might I run into by assigning my macro to this shortcut key combination ???

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
It may work for you, xlbo (and I know you're not trying to be difficult :) ), but as a general rule I think it's not such a good idea. Just MHO. :)

-----------
Regards,
Zack Barresse
 
ok - just wanted to know if there was an actual issue with doing it (killing toolbars / options etc) or whether it was just a bad idea in general.

Cheers

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top