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