Raj asks: I have a 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

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.