Print Entire Workbook

[Budget Programmer]

Hello,
I've complete a somewhat complex Macro that tests out fine. I'm getting
stuck on something that should be simple, but it's a big problem for me.
I'd like to automate the printout of an entire workbook. I'm creating a PDF
file, and Adobe uses Print to create their file.
It's important that all the pages in my workbook be numbered sequentially.
For example, if Worksheet 1 fit on 3 pages, then Worksheet 2 would start with
a page number of 4.
Can't I feed PrintOut the "Print What" section with "Entire Workbook"
somehow?
I've seen other programs loop through worksheets within a workbook, but the
page numbering would reset to 1 with each page. Isn't the "Entire Workbook"
option available to VBA?
Thanks for all your help.

 

[OssieMac]

--
Regards,

OssieMac

 

[OssieMac]

Hi,

I have not tried to create PDF files other than in Excel 2007 which supports
it with a download from Microsoft. Therefore not sure if my answer will help.

Do you mean that you use print to create a pdf and you have to select pdf as
the printer to create the pdf file? If so, if you select File-> print do you
not get the usual dialog box where you select the printer and at the bottom
left there is a section to Print what with the options: Selection, Active
sheet(s) and Entire workbook? This method usually numbers all the sheets
sequentially for the entire workbook.

Your quote "Isn't the "Entire Workbook" option available to VBA?

Usual code for this is:
ActiveWorkbook.Printout Copies:=1, Collate:=True

 

[JLGWhiz]

Using the ActiveWorkbook.PrintOut would pring all of the pages in the
workbook for each worksheet, but it would not accomodate the page numbering
that you want. You would have to create a special file in another
application to get the page numbering.

 

[Shane Devenshire]

Hi,

If you are talking about doing this in Excel 2007 - meaning printing it in
Excel not as a pdf, then you can do it

1. Select all the sheets in the workbook and add page number to the custom
footer or however, you want to do that.
2. Then with all the sheets selected issue the print command, the page
numbering will work between sheets.

 

[OssieMac]

Hi JLGWhiz,

Your quote "but it would not accomodate the page numbering that you want."

Under testing in both xl2002 and xl2007, ActiveWorkbook.PrintOut Copies:=1,
Collate:= True sequentially numbers the pages for the entire workbook.
Shane's suggestion also works in both xl2002 and xl2007. As an added extra,
the page setup can be different for each worksheet but how much different I
am not sure but they can certainly have different headers and the page
numbering is still sequential for the entire workbook and Page X of Y pages
also works.

As per my previous post, selecting file->print and then setting Entire
workbook also works in xl2002 and with xl2007 Microsoft button -> Print ->
Print Select printer ....etc then as for xl2002.

 

[Budget Programmer]

Hello Everyone,
The "ActiveWorkbook.PrintOut Copies:=1, Collate:= True" worked fine. I was
just looking for the "Entire Workbook" parameter of the PrintOut method and
didn't find it. I guess the parameter of "Entire Workbook" is implied in the
"ActiveWorkBOOK.Printout" method.
As far as Adobe Writer goes, it really doesn't matter. Adobe Writer just
configures a printer, and then captures the data that typically goes to a
printer and converts the data to a PDF file. As far as the Office
applications are concerned, it's just another pinter. I tested this with my
HP Officejet and got exactly the same results.
Thanks again Everyone for all your help.