Danny
04-07-2009, 11:25 AM
I am wanting to add a header/footer to every excel document i print with the file name, date, time etc. But the some of the files may already have existing Page Setup options.
So i am wanting to have a macro in my personal.xls book that adds (or overwrites) the current header/footer options for my print out and then sets the header/footer options back the way they were.
My options would look something like this:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Set wb = ActiveWorkbook
Dim sh As Worksheet
Dim sUserName As String
sUserName = Environ$("username")
For Each sh In wb
With sh.PageSetup
.LeftHeader = "&D&T"
.center
.RightHeader = sUserName
.LeftFooter = "&Z&F. TAB &A"
.RightFooter = "&P of &N"
End With
Next sh
End Sub
So i am wanting to have a macro in my personal.xls book that adds (or overwrites) the current header/footer options for my print out and then sets the header/footer options back the way they were.
My options would look something like this:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Set wb = ActiveWorkbook
Dim sh As Worksheet
Dim sUserName As String
sUserName = Environ$("username")
For Each sh In wb
With sh.PageSetup
.LeftHeader = "&D&T"
.center
.RightHeader = sUserName
.LeftFooter = "&Z&F. TAB &A"
.RightFooter = "&P of &N"
End With
Next sh
End Sub