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
|