Copy this programming code below to paste into the Pivot Table Cleanup macro described during my presentation. This is a revised and expanded version of what you may have seen on in my presentations. Each line of code is annotated so that you can see what the macro is doing.
The Copy command is the 4th button on the toolbar at the top of the code window:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
'PivotTableCleanUp Macro 'Removes "Sum of" field prefixes, changes fields from COUNT to SUM, 'and establishes automatic number formatting 'Instructs Excel to skip any lines of code that trigger an error On Error Resume Next 'Capture the name of the current PivotTable strName = ActiveCell.PivotTable.Name 'Checks to see if previous line of code caused an error If Err <> 0 Then 'Notify user of the error MsgBox "You must select any cell within a PivotTable before running this macro.", _ vbCritical, Selection.Address & " is not within a PivotTable" 'Terminate the macro End End If 'Choose the active PivotTable With ActiveSheet.PivotTables(strName) 'Loop through every data field in the PivotTable For i = 1 To .DataFields.Count 'Select a data field With .DataFields(i) 'Change the mathematical function to SUM .Function = xlSum 'Apply number format (add dollar signs or decimals if desired, 'e.g. "$#,##0.00;($#,##0.00)" .NumberFormat = "#,##0;(#,##0)" 'Remove Sum of from field name .Name = Replace(.Name, "Sum of", "") 'Right align field .HorizontalAlignment = xlRight End With 'Loop through the next data field (if applicable) Next End With 'Turn off the error handler On Error GoTo 0 |
