Excel VBA Code to Copy, Paste Special Values

Many of us use formulas to pull data into a specific worksheet, and once we have the data we want to remove the formulas and just keep the data. You could Copy your selection, Right-click, chose Paste Special, select Values, click OK, and then hit Enter, or Esc, to achieve the desired results. Or you could use the code below and assign it to a button to perform all six steps above in one click:

Sub CopyPasteSpecVal()
'
' CopyPasteSpecVal Macro
' This sub will copy & paste 'Special Values' for the selected region, and then unselect the region from the clipboard.
' Written by Frank Heslin 8/25/08
'

'
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Automate It Then Celebrate It!

Frank Heslin

Comments & Questions welcome and encouraged!