Excel Macro to Filter and Print Quick Pivot Table Reports

With a report filter in your pivot table, you can choose a region name, or employee, to show the data for selection only. This macro automatically chooses each item in the report filter field, and prints the pivot table, showing that data. It’s a quick way to print out a set of monthly reports.

Print Pivot Table Macro

The following code will print the pivot table once for each item in the first Report Filter field.

There are 2 lines in the code for printing or previewing the reports:

‘ActiveSheet.PrintOut ‘for printing
- starts with an apostrophe, so this line is “commented out”, and won’t run
ActiveSheet.PrintPreview ‘for testing
- does not have an apostrophe, so it WILL run. You’ll see a preview of each report, instead of printing it
- This line is great for testing the macro, to see how many reports you’ll get, and what they’ll look like

When you’re ready to print:

Remove the apostrophe from the beginning of the ActiveSheet.PrintOut line
Add an apostrophe to the beginning of the ActiveSheet.PrintPreview line

Run the Macro

This macro is in the sample file, that you can download from the Excel Pivot Table Printing page on my Contextures site.

These is a pivot table on the PrintAllItems sheet, and a button that runs the macro.

To see the macro code:

Right-click the Print All Items button
Click Assign Macro
In the Assign Macro window, click the Edit button

Print Pivot Table Macro Code

Here is the code for printing the pivot table, for each item in the first report filter.

Copy this code to a regular code module in your workbook. There are instructions on my Contextures site, and a step-by-step video below the macro.

Sub PrintFirstFilterItems()
'downloaded from contextures.com
 'prints a copy of pivot table
 'for each item in
 'first Report Filter field
On Error Resume Next
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Set pf = pt.PageFields(1)

If pf Is Nothing Then Exit Sub

For Each pi In pf.PivotItems
  pt.PivotFields(pf.Name) _
        .CurrentPage = pi.Name
  'ActiveSheet.PrintOut  'for printing
  ActiveSheet.PrintPreview  'for testing
Next pi
End Sub

Copy Excel Macro Code to a Regular Module

This video shows the steps for pasting a macro into a workbook, and running the macro.

More Pivot Table Macros

For more time-saving macros, go to the Pivot Table Report Filter Macros page on my Contextures site. There are macros to scroll through filters, block selection, sort the report filter fields, and more.

And for more pivot table printing tips and macros, go to the Excel Pivot Table Printing page on my Contextures site.

The Printing page has a macro for pivot tables with multiple report filters. That macro will print the pivot table for each combination of Report Filter items.

Or, you can opt to see a list of all the combinations, instead of printing.
I recommend using the list option first, so you can see how many reports will be printed – it might be more than you expected!

Pivot Table Report Filter Intro

To see the basics for working with pivot table report filters, you can watch this short video.

_______________________