How do I make a button to PasteSpecial Values only.

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I am trying to create a command button that will copy a
cells values only. Expamle:
Copy,PasteSpecial,Values: B10 to B5.
B10 has a =Sum in it and all I want to copy is the amount
in B10 to B5 and I need a button to do this.
Can anyone Help me.

Thanks
Rick
 
tools=>customize

go to the middle tab,

Select edit in the left window

in the right window you will find a PasteSpecial button, drag it to a
toolbar.
 
Try putting this in Personal.xls:

Sub ValuesOnly()

Application.ScreenUpdating = False
On Error Resume Next
If Application.CutCopyMode = False Then
With Selection
.Copy
.PasteSpecial xlValues
End With
With Application
.ScreenUpdating = True
End With
Else
Selection.PasteSpecial xlValues
End If
Application.CutCopyMode = False

End Sub
Sub AddControl()

With CommandBars("Cell").Controls.Add(msoControlButton)
.Caption = "Values"
.FaceId = 1183
.OnAction = "ValuesOnly"
End With

End Sub

Run the second procedure and save personal.xls
You should now have a star on the menu when you right-click a cell
if you copy B5 and then right-click B10 and press the star then just
the values of B5 will be copied.
To re-set the 'cell' menu run this in the immidiate window:

CommandBars("Cell").Reset

be warned - if you've already modified this menu then the
modifications will be lost, in which case run:

CommandBars("Cell").Controls("Values").Delete

Regards,
Jason
 
Back
Top