Paste Special Default

J

Jack Gillis

Most often if not always when I use Paste Special in Excel 2003, I want to
use the Value option. The default is All. Is there any way I can change
the default to Value?

Thank you.
 
G

Gord Dibben

No

You could assign a macro to a button.

Sub Paste_Values()
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP
 
J

Jack Gillis

Yes. Thank you very much. However, I would prefer not to have another
button -- I've more than enough already. But I believe I can use that macro
as a menu item.

Thanks again.
 
G

Gord Dibben

You could add it to right-click menu if you choose.

In Thisworkbook module of your workbook or Personal.xls enter this code.

Private Sub Workbook_Open()
With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Paste Values"
.OnAction = "Paste_Values"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Paste Values").Delete
End Sub


Gord
 
J

Jack Gillis

Super! That does just fine. I wonder if there is a way to move the Paste
Value up from the bottom to just below the Past Special item? No big deal
if it can't be none.

Thanks again very much.
 
G

Gord Dibben

Try this

With Application.CommandBars("Cell").Controls.Add(, , , 5, (temporary))
.Caption = "Paste Values"
.OnAction = "Paste_Values"
End With

See help Controls.Add to suss out the ", , , 5"

Numbers 1 through wharever is the position number of default items on the menu.

You are adding the item to number 5 position just under number 4 which is Paste
Special


Gord
 
Top