Printing worksheets in a workbook with individual Numbering

[robexcelguy]

Hi All,

I have a workbook with approx. 50 worksheets I need to print so that each worksheet is numbered individually (i.e. each worksheet starts at page 1 of [number of pages in the worksheet]) I have tried

&[Page] of &[Pages]

in the footer which works when printing individual worksheets but not the whole workbook.

Is there a macro I can use to add the page numbers before printing?

Ideally I would like to insert Page x of y in a specific cell but that doesn't seem possible.

Any help is greatly appreciated.

Thanks,

Rob

 

[Andrew Poulsom]

You will get what you want if you print each sheet separately (not in Group mode). You can do that with a macro:

Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
    ws.PrintOut
Next ws

 

[robexcelguy]

Thanks for replying, that would work great if I was printing to a normal printer but I'm actually consolidating them into a .pdf file. I can do this by using the built in save as pdf tool or print to our .pdf printer.

Do you know how to get around this problem?

Thanks

 

[Andrew Poulsom]

What code do you get if you record a macro while printing to pdf?