in excel - command: paste special

[kenguru]

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

 

[firefytr]

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

 

[kenguru]

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.

 

[smurfyIAG]

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...

Sub PasteTextValues()
' Keyboard Shortcut: Ctrl+t
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

or

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

 

[smurfyIAG]

:really must proof-read better:
Selection.Furmula = Selection.Value
should be
Selection.Formula = Selection.Value

Sean Murphy
Call Centre Analyst
IAG New Zealand

 

[firefytr]

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

 

[macropod]

Hi Kenguru

You could use code like:

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]

 

[macropod]

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]

 

[xlbo]

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

 

[firefytr]

Ctrl + z ?!?! Oh goodness, I can barely look.

-----------
Regards,
Zack Barresse

 

[kenguru]

Ctrl-z is a good option to, I put ctrl-a
z is quite 'near' to CTRL

Kenguru

 

[xlbo]

...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

 

[firefytr]

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

 

[smurfyIAG]

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

 

[xlbo]

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

 

[firefytr]

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

 

[xlbo]

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