Macro 75: Print Pivot Table for Each Report Filter Item
Pivot tables provide an excellent mechanism to parse large data sets into printable files. You can build a
PivotTable report, complete with aggregations and analysis, and then place a field (like Region) into the
report filter. With the report filter, you can select each data item one at a time, and then print the
PivotTable report.
The macro in this section demonstrates how to automatically iterate through all the values in a
report filter and print.
How it works
In the Excel object model, the Report Filter drop-down list is known as the PageField. To print a
PivotTable for each data item in a report filter, we need to loop through the PivotItems collection
of the PageField object. As we loop, we dynamically change the selection in the report filter, and
then use the ActiveSheet.PrintOut method to print the target range.
Sub Macro75()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a
PivotTable.” Exit Sub
End If
‘Step 4: Exit if more than one page field
If pt.PageFields.Count > 1 Then
MsgBox “Too many Report Filter Fields. Limit 1.”
Exit Sub
End If
‘Step 5: Start looping through the page field and its pivot items
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
‘Step 6: Change the selection in the report filter
pt.PivotFields(pf.Name).CurrentPage = pi.Name
‘Step 7: Set Print Area and print
ActiveSheet.PageSetup.PrintArea = pt.TableRange2.Address
ActiveSheet.PrintOut Copies:=1
‘Step 8: Get the next page field item
Next pi
Next pf
End Sub
1. For this macro, Step 1 declares three variables: pt as the memory container for our
PivotTable, pf as a memory container for our page fields, and pi to hold each pivot item as
we loop through the PageField object.
2. The active cell must be inside a PivotTable for this macro to run. The assumption is that when
the cursor is inside a particular PivotTable, we want to perform the macro action on that
pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found.
We do this by using the ActiveCell.PivotTable.Name property to get the name of
the target pivot.
If the active cell is not inside of a PivotTable, the macro throws an error. This is why we use
the On Error Resume Next statement. This tells Excel to continue with the macro if
there is an error.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt vari-
able is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be
assigned to the variable. If this is the case, the user is notified via a message box, and then we
exit the procedure.
4. Step 4 determines whether there is more than one report filter field. (If the count of
PageFields is greater than one, there is more than one report filter.) We do this check for
a simple reason: We want to avoid printing reports for filters that just happen to be there.
Without this check, you might wind up printing hundreds of pages. The macro stops with a
message box if the field count is greater than 1.
You can remove this limitation should you need to simply by deleting or commenting out Step 4
in the macro.
5. Step 5 starts two loops. The outer loop tells Excel to iterate through all the report filters. The
inner loop tells Excel to loop through all the pivot items in the report filter that currently has
focus.
6. For each pivot item, the macro captures the item name and uses it to change the report filter
selection. This effectively alters the PivotTable report to match the pivot item.
7. Step 7 prints the active sheet, and then moves to the next pivot item. After we have looped
through all pivot items in the report filter, the macro moves to the next PageField. After
all PageFields have been evaluated, the macro ends.
How to use it
You can implement this kind of a macro in a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
