|
|
Search
Contextures Sites
|
||
Excel VBA - Pivot Table Format Macro
|
|
|
Format a Pivot Table in Classic StyleThis sample Excel VBA code from Bob Ryan, of Simply Learning Excel, formats the first pivot table on the active sheet. Download the Format a pivot table in classic style sample file. The file is zipped, and in Excel 2007 format. The macro does the following:
Watch the Format Pivot Table in Classic Style macro video to see the manual formatting time, compared to running the macro. Sub ClassicPlusPivotTableSettings()
'Last modified and tested 2010-07
'Much of this is based on a book by Debra Dalgleish named
' "Excel Pivot Tables Recipe Book: A Problem-Solution Approach", Chapter 13
'
'The following code applies to PivotTable(1) on the active sheet.
'In summary, this macro:
' Applies the Classic PivotTable display, with gridlines and no colors
' Ensures that only data that still exists in
' the data that drives the PivotTable
' will appear in the PivotTable dropdown lists
' Sets all fields to ascending order with no subtotals
' including fields that are not in the Row Labels or Column Labels areas
' For the data field(s) in the Values area,
' changes the setting to Sum, changes the number format, and
' if the field in the Values area is named "Amount" ot "Total Amount"
' it shortens the label
' in the PivotTable to "Sum Amt" or "Sum TtlAmt" respectively.
On Error Resume Next
Application.ScreenUpdating = False
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
'This section applies Classic PivotTable settings
' and turns off the Contextual Tooltips and the Expand/Collapse buttons
With pt
.InGridDropZones = True
.RowAxisLayout xlTabularRow
.TableStyle2 = ""
.DisplayContextTooltips = False
.ShowDrillIndicators = False
End With
'This sets each field in ascending order. It applies this even to fields
' that are not currently in the PivotTable.
For Each pf In pt.PivotFields
pf.AutoSort xlAscending, pf.Name
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
'This command changes the formatting of any field that appears in the Values area
For Each pf In pt.DataFields
pf.Function = xlSum
pf.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Next pf
'These next two sets of statements shorten the description of
' any field in the Values area,
' but only if the field is named "Amount" or "Total Amount"
With pt.PivotFields("Sum of Amount")
.Caption = "Sum Amt"
End With
With pt.PivotFields("Sum of Total Amount")
.Caption = "Sum TtlAmt"
End With
pt.ManualUpdate = False
Application.ScreenUpdating = True
'This ensures that only data that still exists in the data
' that drives the PivotTable
' will appear in the PivotTable dropdown lists
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
End Sub
|
|
|
|
|
|
|
