Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

Learn how to create Excel dashboards.

 

Live-link your Excel dashboards to nearly any web data.

 

 

Learn how to create Excel dashboards.

 

 

 

Excel Pivot Table Printing

  1. Change Value Fields to Vertical Layout For Printing
  2. Check for Pivot Items before Printing
  3. Print Pivot Table for each Page Item
  4. Print Pivot Chart for each Page Item
  5. Print Pivot Table for each Page Item - Multiple Page Fields 
  6. Pivot Table Tutorial List 

Download the zipped sample file for this pivot table tutorial

Change Value Fields 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.

pivot data drag 2010

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

pivot data row labels 2010

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.


 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.

 

Last updated:

May 26, 2013 4:10 PM