Excel VBA

css navigation by Css3Menu.com

Multiple Page Numbers

I was in someone's office and found they had a workbook with about 40 linked sheets. They were jumping through hoops trying to print the sheets with page numbers as though they were all on the same sheet. Here is a quicky to make the nice page numbering AND print the whole document in one swoop.

The line for “Second” is the only thing to change as sheets are added or removed. Makes pretty footers with “Page 2 of 15”, etc..

Option Explicit

Sub PrintBigBook()
    Dim First As Integer, Second As Integer
     Sheets("Sheet1").Activate
     Second = 4     'CHANGE THIS NUMBER WHEN YOU ADD WORKSHEETS
    For First = 1 To Second
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&F!&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page " & First & " of " & Second
        .RightFooter = "©" & Application.Text(Now(),"yyyy")
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintNotes = False
        .PrintQuality = 360
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveSheet.Next.Select
    Next First
	'If you have special pages, include code for them here.
End Sub


© 1997-2017

Updated:  09/19/2017 19:26
This page added:  15 January 1997