Last week, I posted Bob Ryan’s Excel macro for formatting a pivot table in Classic style. Bob’s macro formats the first pivot table indexed on the active sheet.
Dim pt As PivotTable Set pt = ActiveSheet.PivotTables(1)
Modify the Code
Ideally, you’d only have one pivot table on a worksheet, to prevent problems with overlapping, and Bob’s code would work very well. However, as you know, life in Excel isn’t always ideal!
Let’s look at a few scenarios, and how to modify the macro to deal with them.
Select a Pivot Table
In the blog post comments, Yard suggested a variation on the code, so the macro would run on the selected pivot table, to accommodate worksheets with multiple pivot tables.
If a cell in a pivot table isn’t selected, an “Oops” message would be displayed.
On Error Resume Next Set PT = ActiveCell.PivotCell.PivotTable On Error GoTo 0 If PT Is Nothing Then MsgBox "No PivotTable selected", vbInformation, "Oops..." Exit Sub End If
Thanks, Yard, for your sample code. On a multiple pivot table sheet, the user can control which pivot table is formatted.
Format All Pivot Tables on Active Sheet
Taking that idea a bit further, let’s assume you have a worksheet with several pivot table on it. With Yard’s code, shown above, you could select a cell in one of those pivot tables, and run the macro to format that pivot table only.
But, what if you wanted to format all the pivot tables on that sheet? It would take a while to select each pivot table, and run the macro. Instead, you could modify the code, so it formats all the pivot tables on the active sheet.
For Each PT in ActiveSheet.PivotTables 'the formatting code goes here Next PT
Format All Pivot Tables on All Worksheets
Finally, what can you do if there’s more than one worksheet with pivot tables? You don’t want to waste time selecting each worksheet, and running the macro to format all the pivot tables on that sheet.
To loop through the worksheet, you could modify the code, so it formats all the pivot tables on each worksheet in the active workbook.
Dim ws as Worksheet For Each ws In ActiveWorkbook.Worksheets For Each PT in ws.PivotTables 'the formatting code goes here Next PT Next ws
______________
hi,
thanks for the description above.
what if i want the macro to do changes on pivotables 1-3 and 5-6 across various
worksheets, but not for the rest of the the pivots in the workbook?
How about selecting a known list table on a known sheet?
This doesn’t work:
Dim loTable as ListObject
Set loTable = ThisWorkbook.Sheets(“ProgNameLookup”).ListObject(“MyList_table”)
Macro code to “Format All Pivot Tables on Active Sheet” was incredibly useful! I’m not a vba whiz, in fact I don’t really know vba at all; I just record my macros. But I was able to adapt this one to my needs and it will save me an hour or two a month on a recurring process. Excellent! Thank you.
Alan
Thanks, Alan! Glad you were able to adapt the code.