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.

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.

On May 22, 10:50*am, Gord Dibben wrote:
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.- Hide quoted text -

- Show quoted text -

Hi, Gord, thank you for your response. Your macro worked great! :)
Just as a follow up question, though, I tried to modify the code a bit
so that if I had to print to PDF instead of to a printer, it wouldn't
print a separate file for each sheet but would still start each sheet
with page 1 and give the correct page total for each sheet. The
problem I'm running into now is that although I can get it so that
each sheet starts with page 1, the page totals for each sheet are not
correct. It still gives the total number of pages in the entire
workbook, not the total number of pages for each sheet. Is there any
way to do this? Below is the code as I modified it. Thank you in
advance for any information.

Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.CenterHeader = "Page &P of &N"
With ActiveSheet.PageSetup
.FirstPageNumber = 1
End With
Next ws
ActiveWorkbook.PrintOut Copies:=1, preview:=False
End Sub

Hi

Try it this way and see how it looks

Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet.PageSetup
.FirstPageNumber = 1
.CenterHeader = "Page &P of &N"
End With
Next ws
ActiveWorkbook.PrintOut Copies:=1, preview:=False
End Sub

HTH
Mick

On May 23, 6:21*am, "Vacuum Sealed" wrote:
Hi

Try it this way and see how it looks

Sub test()
* * Dim ws As Worksheet
* * For Each ws In ActiveWorkbook.Worksheets
* * * * With ActiveSheet.PageSetup
* * * * * * .FirstPageNumber = 1
* * * * * * .CenterHeader = "Page &P of &N"
* * * * End With
* * *Next ws
* * ActiveWorkbook.PrintOut Copies:=1, preview:=False
End Sub

HTH
Mick

Hi, Vacuum Sealed, thanks for your response. It still prints to PDF
with the total number of pages in the entire workbook rather than the
total number of pages for each sheet. I guess maybe there's no way to
do it when printing to PDF.

If you're talking about the PDF total pages, it should be the number of
printed pages for the entire workbook *if* the entire workbook was
printed.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On May 23, 11:08*am, GS wrote:
If you're talking about the PDF total pages, it should be the number of
printed pages for the entire workbook *if* the entire workbook was
printed.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi, Garry, thanks for your response. Yes, right now it's printing the
totoal number of PDF pages. I'm starting to think there's no way to
get it to print the total pages for each sheet when the entire
workbook is printed to PDF.

OK for printing worksheet at a time but as soon as you introduce the statement

ActiveWorkbook.PrintOut, you have abandoned the sheet by sheet printing.

I am quite sure that printing an entire workbook to a single PDF file will not

respect your page numbering.


Gord

On Mon, 23 May 2011 02:30:58 -0700 (PDT), F J wrote:

Hi, Gord, thank you for your response. Your macro worked great! :)
Just as a follow up question, though, I tried to modify the code a bit
so that if I had to print to PDF instead of to a printer, it wouldn't
print a separate file for each sheet but would still start each sheet
with page 1 and give the correct page total for each sheet. The
problem I'm running into now is that although I can get it so that
each sheet starts with page 1, the page totals for each sheet are not
correct. It still gives the total number of pages in the entire
workbook, not the total number of pages for each sheet. Is there any
way to do this? Below is the code as I modified it. Thank you in
advance for any information.

Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.CenterHeader = "Page &P of &N"
With ActiveSheet.PageSetup
.FirstPageNumber = 1
End With
Next ws
ActiveWorkbook.PrintOut Copies:=1, preview:=False
End Sub

On May 23, 12:12*pm, Gord Dibben wrote:
OK for printing worksheet at a time but as soon as you introduce the statement

ActiveWorkbook.PrintOut, you have abandoned the sheet by sheet printing.

I am quite sure that printing an entire workbook to a single *PDF file will not

respect your page numbering.

Gord



On Mon, 23 May 2011 02:30:58 -0700 (PDT), F J wrote:
Hi, Gord, thank you for your response. *Your macro worked great! :)
Just as a follow up question, though, I tried to modify the code a bit
so that if I had to print to PDF instead of to a printer, it wouldn't
print a separate file for each sheet but would still start each sheet
with page 1 and give the correct page total for each sheet. *The
problem I'm running into now is that although I can get it so that
each sheet starts with page 1, the page totals for each sheet are not
correct. *It still gives the total number of pages in the entire
workbook, not the total number of pages for each sheet. *Is there any
way to do this? *Below is the code as I modified it. *Thank you in
advance for any information.

Sub test()
* *Dim ws As Worksheet
* *For Each ws In ActiveWorkbook.Worksheets
* * * *ws.PageSetup.CenterHeader = "Page &P of &N"
* * * *With ActiveSheet.PageSetup
* * * *.FirstPageNumber = 1
* * * *End With
* * Next ws
* *ActiveWorkbook.PrintOut Copies:=1, preview:=False
End Sub- Hide quoted text -

- Show quoted text -

Hi, Gord, yes, it works for one worksheet at a time but not for the
whole workbook. I guess maybe there isn't a way to do it when
printing to PDF.