How to Revise Pivot Table Macros

There are lots of pivot table macros on this blog, and some of them affect the first pivot table on the worksheet only. Other macros make changes to the selected pivot table only, based on the active cell. See how to pivot tables macros, so the macros make changes to a specific pivot table, or all the pivot tables in a worksheet or workbook.

First Pivot Table

Some macros affect only the first pivot table on the active sheet, such as the code to List All Pivot Field Details.

Near the beginning of that macro, this line finds the pivot table #1 on the worksheet:

Set pt = ActiveSheet.PivotTables(1)

Benefits

works well for sheets with only one pivot table
you don’t need to know the pivot table’s name
the code will continue to work if someone changes the pivot table’s name

Cautions

can be problems if there are two or more pivot tables on the active sheet
the “1” is the pivot table’s index number, not its location on the sheet – PivotTables(1) could be to the right of other pivot tables, or below them
before running the code, be sure you know which pivot table has index number 1

Named Pivot Table

Instead of using an index number, you can refer to a pivot table by name. If you record a macro in Excel, you’ll see the pivot table names in the code:

Set pt = ActiveSheet.PivotTables("SalesPivot")

Benefits

works on sheets with any number of pivot tables
you don’t need to know the pivot table’s index number
the code will continue to work if other pivot tables are added or removed on the sheet

Cautions

the code stops working if someone changes the pivot table’s name

Selected Pivot Table

Another option in macros is to make changes to the pivot table for the active cell. For example, the macro to Remove All Row Fields Code, uses the following line to set the pivot table:

Set pt = ActiveCell.PivotTable

Benefits

you don’t need to know the pivot table’s name
you can choose a different pivot table each time you run the macro, so it’s very flexible

Cautions

you must remember to select a pivot table cell, before running the macro
be sure to select the correct pivot table, because the macro will run if any pivot cell is active

All Pivot Tables on Sheet

Instead of running code on one pivot table, you can change a macro so it affects all pivot tables on the active sheet. Here’s a short macro that prints the name of each pivot table on the active sheet

Sub GetPivotNames()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
  Debug.Print pt.Name
Next pt
End Sub

Benefits

works well for sheets with single or multiple pivot table
you don’t need to know the pivot table names
all the pivot tables are affected, without changing each one individually

Cautions

All the pivot tables are affected – you could modify the code to exclude one or more

All Pivot Tables on All Sheets

Finally, you can change a macro so it affects all pivot tables on all sheets in the active workbook. Here’s a short macro that prints the name of each sheet and pivot table in the active workbook.

Sub GetPivotNamesALL()
Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable
Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
    Debug.Print ws.Name
  For Each pt In ws.PivotTables
    Debug.Print pt.Name
  Next pt
Next ws
End Sub

Benefits

works well for sheets with single or multiple pivot table
you don’t need to know the pivot table names
all the pivot tables in the workbook are affected, without changing each one individually

Cautions

All the pivot tables in the workbook are affected – you could modify the code to exclude one or more sheets or pivot tables

_________________________