Home > Pivot > Macros > Printing Excel Pivot Table Printing Macros and tips for printing pivot tables. For example, print a separate pivot table or pivot chart for each Report Filter item.

 

Change to Vertical Layout For Printing

If you plan to print a pivot table, its usually better to create a vertical layout, instead of having the pivot table spread horizontally across the worksheet.

When value fields are added to the pivot table, by default they are arranged horizontally, and that can make the pivot table very wide, especially if column fields are added too.

Instead of using the horizontal layout, you can change the value fields to a vertical layout.

In the Pivot Table Field List, drag the Values button from the Column Labels area to the Row Labels area.

If there aree other fields in the Row Labels area, it is usually best to place the Values fields below those fields.

Print Pivot Table for Specific Items

The following code loops through a list of product names on the Lists worksheet. The list is in a range named "ProdPrint".

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: Use the Preview:=True setting for testing. When ready to print, change to Preview:=False

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

Print Pivot Table for Each Item

The following code will print the pivot table once for each item in the first Report Filter field. Use the PrintPreview line for testing. When ready to print, remove the apostrophe from the beginning of the ActiveSheet.PrintOut line, and add an apostrophe to the beginning of the ActiveSheet.PrintPreview line.

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

Print Pivot Charts

The following code will print the active pivot chart once for each item in in the first Report Filter field. Use the PrintPreview line for testing.

When ready to print, remove the apostrophe from the beginning of the ActiveSheet.PrintOut line, and add an apostrophe to the beginning of the ActiveSheet.PrintPreview line.

Sub PrintPivotCharts()
'downloaded from contextures.com
 'prints a chart for each item
 'in the first Report Filter field
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim ch As Chart
Set ws = ActiveSheet
Set ch = ws.ChartObjects(1).Chart
Set pt = ch.PivotLayout.PivotTable
Set pf = pt.PageFields(1)

If pf Is Nothing Then Exit Sub

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

End Sub

Print Pivot Table - Multiple Filter Fields

The following code will print the pivot table for each combination of Report Filter items.

When the macro starts, a message box asks if you want to Print (Yes) or List the combinations (No).

This sets the PrintFlag variable to True (Yes) or False (No). If True, the code is currently set to Preview the reports, and you can change the code so it prints instead.

Option Compare Text
Public mrow As Integer
Public PrintFlag As Boolean

Sub PrintAllCombos()
'downloaded from contextures.com
'from code posted by Tom Ogilvy
'September 5 2004
Dim holdSettings
Dim ws As Worksheet
Dim wsPT As Worksheet
Dim strRF As String
Dim strP As String
Dim strP2 As String

Set ws = Worksheets("ComboList")
Set wsPT = ActiveSheet
strRF = "Report Filter items " _
          & "will be listed on sheet "
strP = "Yes = Print, No = Make List"
strP2 = "Print or List?"
mrow = 0

If MsgBox(strP, vbYesNo, strP2) = vbYes Then
  PrintFlag = True
Else
  PrintFlag = False
  MsgBox strRF & ws.Name
End If

If Not PrintFlag Then
  ws.Cells(1, 1).CurrentRegion.Clear
End If

Set PvtTbl = wsPT.PivotTables(1)
wsPT.Activate

If PvtTbl.PageFields.Count = 0 Then
  MsgBox "The PivotTable has no Pages"
  Exit Sub
End If

With PvtTbl
  ReDim holdSettings(1 To .PageFields.Count)
  I = 1
  
  For Each PgeField In .PageFields
    holdSettings(I) _
      = PgeField.CurrentPage.Name
    I = I + 1
    PgeField.CurrentPage = _
       PgeField.PivotItems(1).Name
  Next PgeField

End With

PvtPage = 1
PvtItem = 1
DrillPvt oTable:=PvtTbl, _
    Ipage:=PvtPage, wksht:=ws
I = 1
For Each PgeField In PvtTbl.PageFields
 PgeField.CurrentPage = holdSettings(I)
  I = I + 1
Next PgeField

End Sub

Sub DrillPvt(oTable, Ipage, wksht)
'downloaded from contextures.com

If Ipage = oTable.PageFields.Count Then
 With oTable
  For I = 1 To .PageFields(Ipage) _
                  .PivotItems.Count
   .PageFields(Ipage).CurrentPage = _
   .PageFields(Ipage).PivotItems(I).Name
   mrow = mrow + 1
   slist = ""
   For j = 1 To .PageFields.Count
     slist = slist & _
        .PageFields(j).CurrentPage & " "
   Next j
   If PrintFlag Then
''    ActiveSheet.PrintOut  'print
    ActiveSheet.PrintPreview  'preview
   Else
    For j = 1 To .PageFields.Count
     wksht.Cells(mrow, j).Value = _
      .PageFields(j).CurrentPage.Name
    Next j
   End If
  Next I
 End With
 For I = oTable.PageFields.Count - 1 _
                    To 1 Step -1
   For j = 1 To oTable.PageFields(I) _
                  .PivotItems.Count
     If oTable.PageFields(I).CurrentPage = _
        oTable.PageFields(I) _
          .PivotItems(j).Name Then
        CurrItem = j
        Exit For
     End If
   Next j
   If CurrItem <> oTable.PageFields(I) _
          .PivotItems.Count Then
      oTable.PageFields(I).CurrentPage = _
        oTable.PageFields(I) _
          .PivotItems(CurrItem + 1).Name
      Ipage = I + 1
      DrillPvt oTable, Ipage, wksht
   Else
     If I <> 1 Then
       oTable.PageFields(I).CurrentPage = _
        oTable.PageFields(I).PivotItems(1).Name
     Else
       Exit Sub
     End If
   End If
 Next I
Else
 DrillPvt oTable, Ipage + 1, wksht
End If
End Sub

Get the Sample File

Get the zipped sample file for this pivot table tutorial. The file is in xlsm format and contains macros.

More Links

Pivot Table Layout VBA

Pivot Table Print, Vertical Values

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table