Macro to print from various workbooks and insert page numbers consecutively

**TheGrandeMasterSmag** · 06-21-2011, 09:55 AM

Hi there.

I have a bit of a question that I am hoping someone can provide an answer to...

I currently have an excel macro in place that I use to print off a monthly report. However that report consists of multiple Worksheets and Workbooks. It prints say 5 worksheets from workbook [a] and then 2 worksheets from workbook [b] and then a few more from workbook [a] and so on. In total it has 49 sheets that must be printed.

I have now been asked to apply page numbers to the report.

I have tried the basic way: print it all out, then put it back in the printer and double print the page numbers on it. However, simple in theory, but not when all the paper gets munched up by the printer. DOH!

So I'm wondering if there is some code I could put into my macro that will get it to print a page number onto the page and a consectutive page number on the next printout.

If anyone has a solution to this, I would be VERY grateful!

**realniceguy5000** · 06-21-2011, 10:05 AM

Hi,

Not sure what your code looks like now, However Maybe ?

Not sure where you want the page numbers so you can edit as needed.

Please Login or Register  to view this content.

**TheGrandeMasterSmag** · 06-21-2011, 11:08 AM

Hi! Thanks for your amazing quick reply!

Here is the first bit of the code:

Please Login or Register  to view this content.

How would I fit your code into this macro?

(I only know what I have learnt by hitting record and then looking at the lines, so this is all a bit of a sharp learning curve)

**realniceguy5000** · 06-21-2011, 01:00 PM

Hi,
Based on what you posted this should work for you, However Missing code prevents me from giving you a completed script.

You will need to continue to add to this script. Also if you have sheets you wish not to print you will need to add if statements to control that.

But give this a try at least for the first part.

Please Login or Register  to view this content.

**TheGrandeMasterSmag** · 06-22-2011, 08:37 AM

WOW. BRAIN ACHE...

So... I will need 'IF' statements to eliminate the worksheets that won't be printed.

However, by the looks of things, the code will make consecutive page numbers per workbook?
If the report prints from a number of workbooks and dips in and out of them multiple times, choosing non-consecutive worksheets, the the numbering on the final report won't be consecutive. Is this right? Or does the PG+1 simply act as an independant counter as each worksheet is printed?

(I think I should take a course in writing macros...)

Thanks again for your HUGELY valuable advice, I wasn't even sure if this would be possible...

Regards,

Stephen

**TheGrandeMasterSmag** · 06-23-2011, 08:06 AM

Hi Mike (and anyone else who can help!),

Very excited: I managed to get the macro working printing consecutive page numbers no matter what order the worksheets/workbooks are in! Woo Hoo!

See (some of it) below (as its soooo long, i just did a tester with a few worksheets and workbooks):

Please Login or Register  to view this content.

However....

(Yes, however: there's always a 'however' eh?)

The various different worksheets that I'm printing are either set to print out in landscape or portrait. (This cannot be changed.) This means that the correct page number comes out, but always at the bottom right of the landscape or portait page.

The report, when complete, will be put into a file and viewed in portrait, so the user will find it beneficial to see ALL the numbers at the bottom right, in Portrait.

Is there any way to keep the landscape pages in landscape, but make the page numbers go on to be read as portrait...? (kind of flipped 90 degrees)

(Does this make sense? I've kinda confused myself already!)

Cheers!