Print Every Value In Page Field

September 10, 2002 - by Bill Jelen

Raj asks:

I have an Excel pivot table set up with 150 different values in the page field. How can I run a macro to print each individual page? I tried recording the macro, but it hard codes the name of each page, and some of those pages may not be there from week to week.

Visual Basic makes this a snap. This macro uses the following:

• The PageFields collection will tell you the name of the first page field in the pivot table.
• The PivotItems collection is a list of all the items (pages) in the page field.
• The CurrentPage property allows you to change which page is displayed in the pivot table.

Sub PrintAll()
    ' Find name of page field
    PageField1 = ActiveSheet.PivotTables("PivotTable1").PageFields(1)
    ' Save the name of the page field displayed when macro begins
    OrigPage = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage
    ' 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
        ActiveWindow.SelectedSheets.PrintOut
    Next i
    ' Print the (all) page
    ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = "(all)"
    ActiveWindow.SelectedSheets.PrintOut
    ' Restore view back to original page
    ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = OrigPage
End Sub

A tip of the cap to Raj for reporting back with this hint. The above macro will not work when you have your pivot table sorted under Advanced. Resetting this to manual will correct the problem.