Macro for auto printing Pivot Table with multiple page fields


New Member
Sep 4, 2009
Hi People,

I have a macro I use (below) for auto printing Pivot Tables, which works by selecting an instance of the page field, printing, and then moving onto the next instance in the page field - which works great, except for a problem I have encountered recently which I cant work out how to overcome..

My Pivot table has evolved and now has 3 page fields. The macro I use assumes only one page field is present on the Pivot table and uses that to print from. I can workaround this by elevating whichever page field I want to print from to the first page field in the table layout. But how can I change the coding of the macro below to actually target a specific page field, I presume we could name the page fields and then tell the macro to target this instead of the first page field.

The name of the page field I am trying to print from is 'Stores'

Sub PrintAll()
    ' Find name of page field
    PageField1 = ActiveSheet.PivotTables("PivotTable1").PageFields(1)
    ' Determine how many pages there are in page field
    NumPages = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).PivotItems.Count
    For i = 1 To NumPages
        ThisPage = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).PivotItems(i)
        ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = ThisPage
    Next i
End Sub
Much appreciated, as always

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...