Controlling Headers and Footers

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

Posted in Uncategorized

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.