Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
    
I might be able to help with this but need some more info. Can you send me a sample spreadsheet to work with? I have done things like this in the past. –  user26455 Mar 9 '11 at 22:45
    
How should I get you a sample file? –  Sux2Lose Mar 11 '11 at 14:58
    
If you go in to the Page Setup and on the Header/Footer tab set the Footer to "Page 1 of ?" does that not do what you are after? –  Xantec Mar 19 '11 at 1:01
    
@Xantec No. Printing after each filter is applied results in a single print job with the "?" being the size of that filter. What I want is if I'm printing 15 different one page views I want each page's footer to read "Page X of 15". This solution would only give me 15 pages with reading "Page 1 of 1". –  Sux2Lose Mar 21 '11 at 18:44

2 Answers 2

up vote 3 down vote accepted
+50

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:

enter image description here

  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

...

share|improve this answer
    
+1 for the thorough answer, though this doesn't answer the question. I need the total number of pages to be all the various views' number of pages. So if I'm printing 15 different one page views I want each page's footer to read "Page X of 15". This solution would only give me 15 pages with reading "Page 1 of 1". –  Sux2Lose Mar 21 '11 at 18:41
    
As far as I know, there is no means to determine how many pages will be generated if you printed the current sheet and that is what would be required. You would have to run a macro that would somehow (if possible) mock-print all the stuff to get total number of printed pages, change the footer to that number and then actually print them. It would probably would be easier to just use the page number, print to PDF and use something to combine the PDFs. –  Thomas Mar 21 '11 at 19:07
    
@Sux2Lose - Actually. There may be a way. I'll post an update in a few. –  Thomas Mar 21 '11 at 19:22
    
@Thomas Yeah, that's the workaround I identified. Acrobat makes it easy to import pages from multiple PDFs. I look forward to an update. –  Sux2Lose Mar 21 '11 at 19:36
    
@Sux2Lose - I posted my update. It might just be possible to get the total number of pages assuming that ActiveSheet.HPageBreaks is refreshed when you apply a new filter. That part, I haven't checked. –  Thomas Mar 21 '11 at 22:39

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.