Forum Discussion

JasonChan's avatar
JasonChan
Copper Contributor
Jan 23, 2021

How can i set header only for page 1 and footer for last page?

Hi, I would like to have header on page 1 and footer on the very last page in excel. Can anybody please guide me? TIA
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jan 23, 2021

JasonChan 

Here is a small example with VBA.

 

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial""&8Erstellt von: XXX" & vbLf & "Test"
.CenterFooter = "&""Arial""&8" & ActiveWorkbook.FullName & ": " & ActiveSheet.Name & vbLf & "Seite " & "&P" & " von " & "&N" & "&9"
.RightFooter = "&""Arial""&8Datum: " & Date & vbLf
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial""&8 Erstellt von: XXX" & vbLf & "Test"
.CenterFooter = "&""Arial""&8" & ActiveWorkbook.FullName & ": " & ActiveSheet.Name & vbLf & "Seite " & "&P" & " von " & "&N" & "&9"
.RightFooter = "&""Arial""&8Datum: " & Date & vbLf
End With
End Sub

 

 

Formatting and VBA codes for headers and footers

https://docs.microsoft.com/en-us/office/vba/excel/concepts/workbooks-and-worksheets/formatting-and-vba-codes-for-headers-and-footers

 

 

I would be happy to know if I could help.

 

I wish you continued success with Excel

 

Nikolino

I know I don't know anything (Socrates)

Resources