|
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 are other fields in the Row Labels area, it is usually best to place the Values fields below those fields.

There are three pivot table options, that affect how the pivot table looks when you print it.

To change the any of the settings, follow these steps:
The pivot table Printing option, Repeat row labels, does not make the labels show on each row in the worksheet.
For that, there is a pivot field setting, that you can turn on or off. For example, in the screen shot below, the East region name is shown in each row, instead of showing only once, at the start of each group.

In this short video, I show where that setting is, and the change that it makes to the pivoot table. The written steps are on the Excel Pivot Table Field Settings page.
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: July 2, 2024 12:18 PM