Paste Special Default

  • Thread starter Thread starter Jack Gillis
  • Start date Start date
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.
 
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
 
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.
 
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
 
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.
 
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
 
Perfect! Thanks again.

Gord Dibben said:
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
Click to expand...
 
Back
Top