Paste Special

[John Tripp]

How can I create a menu item under Edit to execute the equivalent of the menu
commands Edit-Paste Special-Values? Thanks

 

[Gord Dibben]

Tools>Customize>Commands>Macros.

Drag the Custom Menu Item to the Edit Menu.

Assign this macro to the Custom Item

Sub Paste_Values()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

 

[John Tripp]

Gord This is not exactly what I need.
I will already have copied a range to the clipboard.
I now want to select another range and copy the clopboard to the newly
selected range. Thanks

 

[Gord Dibben]

Remove the Selection.Copy line.

Not sure why I added it to start with.

Sub Paste_Values()
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub


Gord

 

[John Tripp]

Gord, thanks. Even though it is simple, you saved me time in trial and
error. This is a big help. I actually modified it to do values, formats and
column width all at once. This is the most common thing i do when I do these
copies. Now i only wish I could add it to the popup menu that comes up when
I right click on a cell. Is that possible

 

[John Tripp]

Gord, thanks. Even though it is simple, you saved me time in trial and
error. This is a big help. I actually modified it to do values, formats and
column width all at once. This is the most common thing i do when I do these
copies. Now i only wish I could add it to the popup menu that comes up when
I right click on a cell. Is that possible

 

[Gord Dibben]

Copy to Thisworkbook Module.

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

Always a good idea to delete first then re-create so's you don't get more than
one copy of the item on the right-click menu.

If you want it for just one workbook, put the code above in that workbook then
add a BeforeClose event to delete it.

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

I have all my macros and functions in an add-in named MyMacros.xla.

You will adjust to the workbook that contains your macros.

Maybe Personal.xls?


Gord

 

[John Tripp]

Thanks again. You have given me some good tips and I can see how to use them
in other situations.