Excel Pivot Table Printing Macro

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.

____________________

Save

Save