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 Tutorials

Pivot Table Introduction 
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007 
Custom Calculations 
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data  
Filters, Top 10 
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache   
PivotTable Style
Printing a Pivot Table   
Protection  
Running Totals  
Show and Hide Items 
Sorting
Subtotals 
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007) 
Pivot Tables Recipe Book (Excel 2003) 
Pivot Tables Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Power 
Pivot Play PLUS 

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

 

Learn how to create Excel dashboards.


       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2012
All rights reserved.

 

Last updated:

December 3, 2012 11:35 AM