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

______________