Home > Pivot > Macros > Printing Excel Pivot Table PrintingMacros and tips for printing pivot tables. For example, print a separate pivot table or pivot chart for each Report Filter item. |
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.
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:
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
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
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
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 zipped sample file for this pivot table tutorial. The file is in xlsm format and contains macros.
Last updated: January 30, 2023 4:27 PM