Using CTRL+V to paste values (by default)

[Rudi]

Hi,

I was asked this question recently:

Can you tell me if there is a way to change my excel settings so that when I copy and paste, it pastes the value and not the formula. I know of a few ways, but if there is a way to make it my default (by only using ctrl C and ctrl V), it would save me a lot of time.

I answered:
The only way to set paste special: values as the default, is to force a macro to do it on those key strokes. There is no other way to modify Excel to do this. An alternative is to do this is one of the following:

- Position you mouse pointer over the edge of the selected range and drag to the destination using the RIGHT mouse button. When you release the button, you can choose “Copy here as values only” from the context menu.

- Or; Select and CTRL+C to copy, then select destination and CTRL+ALT+V (to paste special). Press V (for values) and ENTER.

Is there another way; or if it has to be a macro, how will one create this to capture the keystrokes to use CTRL+V to paste as values only?

TX

[sdckapr]

I have added the "Paste Values" toolbar button on my toolbar next to the copy button.

You can create the code
Sub PasteValues()
Selection.PasteSpecial Paste:=xlValues
End Sub

And assign it to ctrl-v (or even Ctrl-V so that lower case will paste as normal) and upper case will paste values).

Steve

[HansV]

The user could create a macro in their personal macro workbook:

Code: Select all

Sub PasteValues()
  Selection.PasteSpecial Paste:=xlPasteValues
End Sub

and assign it to Ctrl+v:
- Press Alt+F8 in Excel to activate the Macros dialog.
- Select Personal.xls!PasteValues (in Excel 2007 and later, it'll be Personal.xlsb!PasteValues)
- Click Options...
- Type v in the shortcut key box.
- Click OK.

The Paste button will still perform the standard action.

[Rudi]

Thanks again! This is perfect!

[VegasNath]

Thanks again! This is perfect!

Rudi, it may well be worth your while to keep in mind that every time you run the paste special macro, undo will be lost. This would be too big a loss (for me).

[Rudi]

Thanks again! This is perfect!

Rudi, it may well be worth your while to keep in mind that every time you run the paste special macro, undo will be lost. This would be too big a loss (for me).

Thanks Vegasnath. I am aware of this and I did mention it to the person who requested this solution from me.
Cheers