Linda wants to print the header on the first page only and the footer on the last page only. I don’t know of any way to do that except through code. I used the BeforePrint event in the ThisWorkbook module. The macro uses the existing header and footer and creates between 1 and 3 different print jobs. Because it uses the existing data, I wasn’t sure how to handle multiple sheets which could have different headers and footers. So I didn’t handle them, I skipped them.
Here’s the code, let me know what you think or how to make it better. I wish I could loop through the different headers and footers, but I couldn’t figure out a slick way to do that.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lPageNum As Long
Dim i As Long
Dim sHeader(1 To 3) As String
Dim sFooter(1 To 3) As String
‘Print normally if more than one sheet is selected
If ActiveWindow.SelectedSheets.Count = 1 Then
‘Cancel the normal print
Cancel = True
‘Get the total number of pages to print
lPageNum = Application.ExecuteExcel4Macro(“Get.Document(50)”)
‘Store the current headers and footers
With ActiveSheet.PageSetup
sHeader(1) = .LeftHeader
sHeader(2) = .CenterHeader
sHeader(3) = .RightHeader
sFooter(1) = .LeftFooter
sFooter(2) = .CenterFooter
sFooter(3) = .RightFooter
‘Loop through the pages
For i = 1 To lPageNum
‘Set header for the first page
‘clear header for non-first pages
If i = 1 Then
.LeftHeader = sHeader(1)
.CenterHeader = sHeader(2)
.RightHeader = sHeader(3)
Else
.LeftHeader = “”
.CenterHeader = “”
.RightHeader = “”
End If
‘Set footer for last page
‘clear footer for non-last pages
If i = lPageNum Then
.LeftFooter = sFooter(1)
.CenterFooter = sFooter(2)
.RightFooter = sFooter(3)
Else
.LeftFooter = “”
.CenterFooter = “”
.RightFooter = “”
End If
‘if it’s the first or last page or both,
‘print that page
Application.EnableEvents = False
If i = 1 Or i = lPageNum Then
ActiveSheet.PrintOut i, i
‘if it’s the second page, print all middle
‘pages
ElseIf i = 2 Then
ActiveSheet.PrintOut i, lPageNum – 1
End If
Application.EnableEvents = True
Next i
‘restore the headers and footers
.LeftHeader = sHeader(1)
.CenterHeader = sHeader(2)
.RightHeader = sHeader(3)
.LeftFooter = sFooter(1)
.CenterFooter = sFooter(2)
.RightFooter = sFooter(3)
End With
End If
End Sub
Posting code? Use <pre> tags for VBA and <code> tags for inline.