Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.CenterHeader = "Page &P of &N"
ws.PrintOut preview:=True
Next ws
End Sub


Gord Dibben MS Excel MVP


On Sun, 22 May 2011 02:11:31 -0700 (PDT), F J wrote:

Hi, using VBA, is there any way to print an entire workbook and have
Excel print each worksheet with the page numbers starting at page 1 on
each sheet? In other words, if Sheet1 has 10 pages and Sheet2 has 5
pages, I would like those pages to be numbered 1-10 and 1-5 when they
print, instead of the page numbers running from 1-15. With small
files with only a few sheets it's easy enough to just print each sheet
separately, but in files with many sheets it can be a pain to print
each sheet individually.

In any case, to my knowledge, there is no way to do this in native
Excel, so I was wondering if it is possible with VBA.

Thanks in advance for any information.