Excel VBA: Run Macro on Specific Pivot Tables

image 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


You may also like...

4 Responses

  1. mike says:

    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?

  2. Toby says:

    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”)

  3. Alan says:

    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.


Leave a Reply

Your email address will not be published. Required fields are marked *