Use this macro from Bob Ryan, of Simply Learning Excel, to format a pivot table in classic style, similar to the pivot tables in older versions of Excel
This sample Excel VBA code from Bob Ryan, of Simply Learning Excel, formats the first pivot table on the active sheet.
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
Download the Format a pivot table in classic style sample file. The file is zipped, and in Excel xlsm format.
Last updated: April 22, 2021 7:33 PM
Contextures RSS Feed