Excel -- Pivot Tables -- 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
Download the zipped sample file

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()

'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()
 '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()
 '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()
'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)
'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

 

 

1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items  
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing  
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache     
12. Pivot Tables -- Protection   
13. Pivot Tables -- Grand Totals
 
       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated:

July 18, 2008 11:47 PM