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!

Excel VBA Code for Headers & Footers

Here is the code to add Headers & Footers to your excel worksheets with just one click:

Sub HeaderandFooter()
'
' HeaderandFooter Macro
' This sub will add "Prepared by" and the "Date & Time" in the Header
' and the "Filename & Path" and # of pages in the Footer.
' Written by Frank Heslin 8/25/08


'
With ActiveSheet.PageSetup
.LeftHeader = "&8Prepared by: " & Application.UserName
.RightHeader = "&8Last Printed &D &T"
.LeftFooter = "&8&Z&F &A"
.RightFooter = "&8&P of &N"
.LeftMargin = Application.InchesToPoints(0.4)
.RightMargin = Application.InchesToPoints(0.51)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.Orientation = xlPortrait ' for landscape replace xlPortrait with xlLandscape

End With
ActiveWindow.SelectedSheets.PrintPreview

End Sub ' H&F

Paste this code into a module, and either add the macro to the QAT (2007) or add a button and assign the macro (2003).