Usually, pivot tables are used for exploring data in the workbook. Occasionally though, you might need to print out a pivot table, to include in a report. To help with that, use the pivot table printing macro below, and visit my website for more examples.
Print a Pivot Table
In an Excel pivot table, you can add Report Filters at the top. Then, select one or more items from those filters, to see a summary for a specific part of the data.
If you need to print out a pivot table, you can manually print the worksheet, after selecting items from one or more of the Report Filters.
Or, to automate the printing, use a pivot table macro, like the one below.
Macro to Print Specific Items
This macro will run through a list of product names on a worksheet, and print a copy of the pivot table for each product.
In the sales data, there are several products listed. Only 3 of those products are listed in a range named “ProdPrint”, shown below.
Print With a Macro
To print the pivot table for just those 3 products, use the macro that is listed below. Here’s how it works.
When the code runs, it does the following, for each item in the ProdPrint list:
- It looks for that product name in the Product Report Filter (called “pagefield” in the macro)
- If the product name is found, the Report Filter is changed, and the pivot table is printed.
NOTE: If you’re testing the macro, use the Preview:=True setting. Later, if you’re ready to print, change that line to Preview:=False
The Macro Code
Here’s the macro code – copy it to a regular code module in your workbook. There are instructions for that on my Contextures website.
Sub PrintPivotForList() 'downloaded from contextures.com 'print pivot table for 'products in list Dim ws As Worksheet Dim wsL As Worksheet Dim pt As PivotTable Dim pi As PivotItem Dim str As String Dim rng As Range Dim c As Range Set ws = ActiveSheet Set wsL = Worksheets("Lists") Set pt = ws.PivotTables(1) Set rng = wsL.Range("ProdPrint") For Each c In rng Set pi = Nothing str = c.Value With pt.PageFields("Product") On Error Resume Next Set pi = .PivotItems(str) On Error GoTo 0 If pi Is Nothing Then Debug.Print str & " was NOT printed" Else .CurrentPage = str ws.PrintOut Preview:=True End If End With Next c End Sub
More Pivot Table Printing Macro Examples
There are more pivot table printing macro examples on my Contextures website.
There is also a free workbook that you can download, with sample pivot tables, and the macros, and buttons to run them.