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.

 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
 With ActiveSheet.PageSetup
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&P"
 End With
End Sub

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

Hi! Thanks for your amazing quick reply!

Here is the first bit of the code:

Sub print_order()
'
' print_order Macro
'

'
    Sheets("aaa").Select
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Cons. Orders").Select
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Bank & Cash").Select
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Grid Sch 1").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Sch 2").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Sch 2(a)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("OHD Sch 10").Select
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Overhead Analysis").Select
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Workbooks.Open Filename:="K:\ACCOUNTS\Overhead Analysis.xlsx"
    Sheets("Actual").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Windows("Accounts - May 2011 - MMA's.xls").Activate
    Sheets("Sales & Contribution Table").Select
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ETC ETC ETC for maaaany more lines!!!!

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.

 Sub sheetme()

    Dim Thiswb As Workbook
    Dim Thatwb As Workbook
    Dim wks As Worksheet
    Dim PG As Integer
    
    PG = 0
    Set Thiswb = ThisWorkbook
       
        For Each wks In Thiswb.Worksheets
       
        PG = PG + 1
        With wks.PageSetup
          .RightFooter = "Page " & PG
        End With
            wks.PrintOut Copies:=1
        Next wks
    
    Set Thatwb = Workbooks.Open("K:\ACCOUNTS\Overhead Analysis.xlsx")
    
        For Each wks In Thatwb.Worksheets
        PG = PG + 1
            With wks.PageSetup
                .RightFooter = "Page " & PG
            End With
            wks.PrintOut Copies:=1
            
        Next wks
        
        ' just keep adding workbooks or worksheets
    
    
End Sub

**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):

Sub TEST_PAGE_NO()
'
' TEST_PAGE_NO Macro
'

'
    Dim PG As Integer
    
    PG = 0
    
    Workbooks.Open Filename:= _
        "K:\ACCOUNTS\MMA's\MMA FY 2011\Accounts - May 2011 - MMA's.xls"
    Sheets("aaa").Select
    PG = PG + 1
    With ActiveSheet.pagesetup
        .RightFooter = "Page " & PG
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Grid Sch 1").Select
    PG = PG + 1
    With ActiveSheet.pagesetup
        .RightFooter = "Page " & PG
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Workbooks.Open Filename:= _
        "K:\ACCOUNTS\Month End Schedules\FY 10-11\Sales by Brand Analysis.xls"
    Sheets("Sales_Prods").Select
    PG = PG + 1
    With ActiveSheet.pagesetup
        .RightFooter = "Page " & PG
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Windows("Accounts - May 2011 - MMA's.xls").Activate
    Sheets("Sch 3").Select
    PG = PG + 1
    With ActiveSheet.pagesetup
        .RightFooter = "Page " & PG
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut Copies:=1


End Sub

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!