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-2024

Updated:  06/21/2024 07:42
This page added:  15 January 1997