Excel VBA: Run Macro on Specific Pivot Tables
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)
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