Paste Special Default

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack Gillis - 08 Mar 2008 11:03 GMT

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.

Reply to this Message

Gord Dibben - 08 Mar 2008 16:27 GMT

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

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

Reply to this Message

Jack Gillis - 08 Mar 2008 17:51 GMT

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.

> No
>
[quoted text clipped - 14 lines]
>>
>>Thank you.

Reply to this Message

Gord Dibben - 08 Mar 2008 18:33 GMT

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

>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
[quoted text clipped - 20 lines]
>>>
>>>Thank you.

Reply to this Message

Jack Gillis - 08 Mar 2008 20:13 GMT

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.

> You could add it to right-click menu if you choose.
>
[quoted text clipped - 45 lines]
>>>>
>>>>Thank you.

Reply to this Message

Gord Dibben - 08 Mar 2008 21:14 GMT

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

>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
[quoted text clipped - 51 lines]
>>>>>
>>>>>Thank you.

Reply to this Message

Jack Gillis - 08 Mar 2008 22:38 GMT

Perfect! Thanks again.

> Try this
>
[quoted text clipped - 74 lines]
>>>>>>
>>>>>>Thank you.

Reply to this Message