Printing a dynamic sheet as one document

Question

I have a spreadsheet structured as follows:

• Summary section at the top
• Detail section on the bottom
• Summary section summarizes the detail section which is filtered using auto filters

There are ten products that all need to be printed individually, but I want the page footer to show the overall page position of all the print jobs and the total number of pages.

That is probably not clear. So for example, if I print the two page Product A view it will print page 1 of 2 and 2 of 2. If I print the one page Product B it will show page 1 of 1. What I want is to print both and have Product A show Page 1 of 3, Page 2 of 3, and Product B be Page 3 of 3. Is there any way to accomplish this?

UPDATE: I was hoping to have an Excel only answer, but no one has been able to provide one. I will post a workaround that I am using. I'll leave this open a little longer before I select my own answer.

UPDATE 2: Thomas has the right answer - Excel/VBA only!

Answer 1

There are two different questions here.

1. How to put page numbers in the footer
2. How to run apply multiple autofilters printing after each filter is applied.

To the first, you can do that in Page Setup:

1. Go into Page Setup:
2. Click Header/Footer
3. Click the "Custom Footer..." button
4. In one of the sections enter "Page &[Page] of &[Pages]". This represents a code to display Page 1 of 2, Page 2 of 2 etc. at the bottom of each page.

To accomplish the second task, you need a Macro which means you need to save the file as a Macro-enabled file (xlsm). Once you do that, you need to show the Developer tab by:

1. Clicking on the flower in the top-left corner of the Excel window, choose "Excel Options"
2. Ensure the section named "Popular" is selected
3. Ensure that "Show Developer tab in Ribbon" is checked.

Now that you have the buttons visible to manage macros, you need to record a macro which will let you automate the process of applying various filters and printing after each one.

1. Click on the Developer tab
2. Click the"Record Macro" button.
3. Once recording is on, run through the process you want to automate. I.e., apply a filter, print, apply another filter, print etc.

Your macro will be written in VBA (Visual Basic for Applications) and come out something like the following:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
ActiveSheet.Range("$A$3:$A$568").AutoFilter Field:=1, Criteria1:="=a*", _
    Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
ActiveSheet.Range("$A$3:$A$568").AutoFilter Field:=1, Criteria1:="=b*", _
    Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False

---

Addition

Given what you have mentioned, I think you can get close using a function like so:

Public Sub GetTotalPageCount()
    Dim horizontalBreaks As Integer
    Dim verticalBreaks As Integer

    horizontalBreaks = ActiveSheet.HPageBreaks.Count + 1
    verticalBreaks = ActiveSheet.VPageBreaks.Count + 1

    GetTotalPageCount = horizontalBreaks * verticalBreaks
End Sub

To acheive the overall result you want, you would need to build something that would do something akin to the following pseudo-code:

• Apply filter
• Add page count to global var
• Apply filter
• Add page count to global var

...

• (After last filter)ActiveSheet.PageSetup.CenterFooter = "Page &P of " & g_TotalPages
• Apply filter
• Print
• Apply filter
• Print

...

Answer 2

The workaround I am using works as follows:

1. Use VBA to change my printer to Adobe Acrobat Pro
2. Using VBA, cycle through my filters to print each view into a different PDF file using the filter as a file name.
3. Import all the files into one master file in Adobe Acrobat.
4. Add page numbers in Adobe Acrobat.

I was really hoping to have an Excel/VBA only solution, but I'm not sure that's possible.