Search Contextures Sites

Excel Pivot Table Tutorial -- Printing

  1. Check for Pivot Items before Printing
  2. Print Pivot Table for each Page Item
  3. Print Pivot Chart for each Page Item
  4. Print Pivot Table for each Page Item - Multiple Page Fields 
  5. Pivot Table Tutorial List 

Download the zipped sample file for this pivot table tutorial

 

  

Check for Pivot Items Before Printing

The following code loops through a list of employee names on the Lists worksheet. It will test for each name in the Employee page field, before changing the current page. If the employee name is an item in the field, the page field will be changed, and the pivot table will be printed. Use the Preview:=True setting for testing. When ready to print, change to Preview:=False 

Sub ChangePivotPage()
'pivot table tutorial by contextures.com

'Test if Item exists
'before setting the CurrentPage to that item
Dim ws 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 pt = ws.PivotTables(1)
Set rng = Worksheets("Lists").Range("EmpNames")

For Each c In rng
  Set pi = Nothing
  str = c.Value
  With pt.PageFields("Employee")
    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 Page Item

The following code will print the pivot table once for each item in the page field (assumes there is one page 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 PrintPivotPages()
'pivot table tutorial by contextures.com
 'prints a copy of pivot table for each item in page field
 'assumes one page field exists
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
  For Each pf In pt.PageFields
    For Each pi In pf.PivotItems
      pt.PivotFields(pf.Name).CurrentPage = pi.Name
'      ActiveSheet.PrintOut  'use this for printing
      ActiveSheet.PrintPreview  'use this for testing
    Next
  Next pf
End Sub


 

 

Print Pivot Chart for each Page Item

The following code will print the pivot chart once for each item in the page field (assumes there is one page 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()
'pivot table tutorial by contextures.com
 'prints a chart for each item in the page field
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveChart.PivotLayout.PivotTable
  For Each pf In pt.PageFields
    For Each pi In pf.PivotItems
      pt.PivotFields(pf.Name).CurrentPage = pi.Name
'      ActiveSheet.PrintOut
      ActiveSheet.PrintPreview  'print preview for testing
    Next
  Next pf
End Sub


 

 

Print Pivot Table for Each Page Item - Multiple Page Fields

The following code will print the pivot table for each combination of page items. If PrintFlag is not set to true, descriptive information is written to the PageItemList worksheet.

Option Compare Text
Public mrow As Integer
Public PrintFlag As Boolean
'==========================================

Sub PrintAllPages()
'pivot table tutorial by contextures.com
'from code posted by Tom Ogilvy
'September 5 2004
Dim holdSettings
Dim ws As Worksheet
Dim wsPT As Worksheet
Set ws = Worksheets("PageItemList") 'sheet for page items
Set wsPT = Worksheets("Pivot") 'sheet with PivotTable
mrow = 0
If MsgBox("Print?", vbYesNo, "Print?") = vbYes Then
  PrintFlag = True
Else
  PrintFlag = False
  MsgBox "Page field items will be listed on sheet " & 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)
'pivot table tutorial by contextures.com
'Debug.Print "in DrillPvt, page:=" & Ipage & " Page Item: " & _
'  oTable.PageFields(Ipage).CurrentPage & " " & mrow
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
 '  Debug.Print slist
   If PrintFlag Then
''    ActiveSheet.PrintOut  'print the sheet
    ActiveSheet.PrintPreview  'preview -- for testing
   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

Download the zipped sample file for this pivot table tutorial

 

 

 

Pivot Table Tutorial List

Excel Pivot Table -- Introduction 
Excel Pivot Table -- Create a Pivot Table in Excel 2007 
Excel Pivot Table -- Data Field Layout
 Excel Pivot Table -- Show and Hide Items
Excel Pivot Table -- Clear Old Items
Excel Pivot Table -- Field Settings
Excel Pivot Table -- GetPivotData
Excel Pivot Table -- Grouping Data
Excel Pivot Table -- Multiple Consolidation Ranges
Excel Pivot Table -- Printing   
Excel Pivot Table -- Custom Calculations 
Excel Pivot Table -- Pivot Cache     
Excel Pivot Table -- Protection  
Excel Pivot Table -- Grand Totals
Excel Pivot Table -- Running Totals  
Excel Pivot Table -- Filter Source Data  

Pivot Table Blog -- Related Posts

Repeat Pivot Table Headings When Printing  

 
 
Learn how to create Excel dashboards.

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Last updated:

February 6, 2010 1:27 AM