Search
Contextures Sites
| |||
|
|
|
||
|
|
|||
|
|
|
|
|
Search Contextures Sites
Excel Pivot Table Printing
- Change Value Fields to Vertical Layout For Printing
- Check for Pivot Items before Printing
- Print Pivot Table for each Page Item
- Print Pivot Chart for each Page Item
- Print Pivot Table for each Page Item - Multiple Page Fields
- 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.
If there aree other fields in the Row Labels area, it is usually best to place the Values fields below those fields.
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
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated:
May 26, 2013 4:10 PM
