Excel VBA: Get a PivotTable Name

VBA & macros · Updated November 21, 2026

In Excel VBA, get a PivotTable’s name from its Name property: pt.Name. First obtain the PivotTable object from a worksheet’s PivotTables collection, such as Set pt = Worksheets("Summary").PivotTables(1); then read its name or print it in the Immediate window.

Read the name of a specific PivotTable

Every PivotTable object has a Name property. The collection belongs to a worksheet, so the common pattern is to identify the worksheet, retrieve the PivotTable from that sheet, and inspect its name. For example, the following macro prints the first PivotTable’s name on the worksheet called Summary:

Sub ShowFirstPivotTableName()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Use the worksheet that contains the PivotTable.
    Set ws = ThisWorkbook.Worksheets("Summary")

    If ws.PivotTables.Count = 0 Then
        Debug.Print "No PivotTables on " & ws.Name
        Exit Sub
    End If

    Set pt = ws.PivotTables(1)
    Debug.Print pt.Name
End Sub

The number in PivotTables(1) means the first PivotTable in that worksheet’s collection; it does not mean a PivotTable named “1.” You can also look up an object by its existing name, for example ws.PivotTables("PivotTable1").Name. Replace Summary and PivotTable1 with names from your workbook. If you want the name displayed in a worksheet cell instead of the Immediate window, assign it to a cell, such as ws.Range("A1").Value = pt.Name.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even though this example only reads and displays a name. Store a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code.

List every PivotTable and its name

When you do not know which PivotTables exist, loop through the worksheets and each worksheet’s PivotTables collection. This reports the worksheet name alongside each PivotTable name, which is useful because a name on its own may not tell you where the object is located.

Sub ListPivotTableNames()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Visit each worksheet in the workbook that contains this macro.
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            Debug.Print ws.Name & vbTab & pt.Name
        Next pt
    Next ws
End Sub

Open the Visual Basic Editor’s Immediate window to see the output; in the editor, use View > Immediate Window. Each line contains a worksheet name followed by a tab and the PivotTable name. A worksheet with no PivotTables simply contributes no lines. This approach is safer than guessing generated names such as PivotTable1, because users can rename PivotTables and generated numbering can vary between workbooks.

ThisWorkbook means the workbook containing the VBA code. That is often what you want when a macro is stored in the workbook being inspected. ActiveWorkbook, by contrast, means the workbook currently active in Excel. If code is in Personal.xlsb or an add-in, those references can point to different files, so choose deliberately. To inspect whichever file is active, change the loop to For Each ws In ActiveWorkbook.Worksheets.

Get the name of the PivotTable under the active cell

If a user selects a cell inside a PivotTable and wants to identify that specific object, use the active cell’s PivotTable property. Check that the active sheet is a worksheet, then handle the case where the selected cell is outside a PivotTable:

Sub ShowSelectedPivotTableName()
    Dim ws As Worksheet
    Dim pt As PivotTable

    If TypeOf ActiveSheet Is Worksheet Then
        Set ws = ActiveSheet
    Else
        MsgBox "Select a cell on a worksheet."
        Exit Sub
    End If

    Set pt = Nothing
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    On Error GoTo 0

    If pt Is Nothing Then
        MsgBox "The active cell is not in a PivotTable."
    Else
        MsgBox "PivotTable name: " & pt.Name
    End If
End Sub

The error-handling lines are limited to retrieving the object, where a selection that does not belong to a PivotTable may not provide one. The macro then checks whether pt was set before trying to use it. This is more informative than assuming that the active cell always belongs to a PivotTable. A selected chart, a blank cell, or a cell in an ordinary range is not enough to identify a PivotTable.

Choose the right workbook and worksheet reference

The object path matters because Excel exposes PivotTables through worksheets. The expression Worksheets("Summary").PivotTables searches one named sheet, while ActiveSheet.PivotTables depends on which sheet the user has selected. A workbook can contain several worksheets with PivotTables, and the same sheet can contain several separate PivotTables.

  • Use a worksheet name when the macro should always inspect a known sheet. This avoids results changing just because someone clicked another tab.
  • Use a worksheet variable when the code already loops through sheets or will read several PivotTable properties.
  • Use ActiveSheet for an interactive macro that intentionally follows the user’s current selection. Confirm that it is a worksheet, not a chart sheet.
  • Use ThisWorkbook or ActiveWorkbook to specify which file to inspect. Do not assume they refer to the same workbook.

The collection approach applies to PivotTables placed on worksheets. It does not mean that a PivotChart has its own unrelated PivotTable name: a PivotChart is associated with a PivotTable, and the underlying table is the object whose name you read. If a workbook contains multiple PivotTables, identify the correct worksheet and object rather than relying on a chart’s displayed title.

PivotTable name versus source, sheet, and field names

“PivotTable name” is easy to confuse with several other names visible in Excel. The VBA property pt.Name identifies the PivotTable object. It is not the worksheet tab name, the name of the source table or range, the PivotTable’s caption, or any row, column, or value field label. These values answer different questions and are not interchangeable.

  • PivotTable object name: use pt.Name to identify the PivotTable in VBA or refer to it through a worksheet’s PivotTables collection.
  • Worksheet name: use ws.Name to identify the sheet that contains the PivotTable.
  • Source information: a PivotTable may be based on a range, an Excel table, or another supported data source. Its source is not returned by pt.Name.
  • Field or item captions: labels such as “Region” or “Sum of Sales” describe fields or displayed results, not the PivotTable object itself.

For example, a PivotTable can be named ptSalesByRegion while appearing on a sheet named Summary and using a source table named SalesData. Reading pt.Name returns the first of those identifiers. If a report needs to show both location and object identity, print ws.Name and pt.Name together, as in the listing macro above.

Rename a PivotTable when a stable name is useful

Excel often gives newly created PivotTables default names, but those names are not a good basis for long-lived code if users may rename, delete, or recreate the tables. A meaningful name makes a macro easier to understand. You can set the object’s Name property in VBA, for example pt.Name = "ptSalesByRegion", or change the name in Excel’s PivotTable controls. In Microsoft 365 for Windows, select a cell in the PivotTable and use PivotTable Analyze > PivotTable Name.

Choose a concise, distinctive name and verify it after assignment by reading pt.Name again. Avoid assuming that a particular default name will always be assigned when a PivotTable is created. If code depends on a name, make sure the workbook’s setup process gives the PivotTable that name and handles the possibility that the expected sheet or table is missing. An error caused by looking up a nonexistent name is different from a PivotTable returning an empty name; the object lookup itself failed.

Renaming is optional when the task is simply to display or log existing names. If you only need to inspect a workbook, avoid changing names as a side effect. This also keeps the macro’s purpose clear: reading pt.Name is a lookup, while assigning to it changes the workbook’s structure.

Common errors and practical fixes

  • “Subscript out of range” for a worksheet: the worksheet name may be misspelled, or the code may be looking in the wrong workbook. Check the tab name and whether you need ThisWorkbook or ActiveWorkbook.
  • “Subscript out of range” for a PivotTable: the worksheet may have no PivotTables, or the name or index is incorrect. Check ws.PivotTables.Count before selecting by index and confirm the exact name before looking it up.
  • The macro finds no PivotTables: confirm that the objects are actual PivotTables on worksheets, not ordinary formatted ranges that look like summaries. Also confirm that the macro is inspecting the intended workbook.
  • The active-cell macro reports no table: select a cell inside the PivotTable body or its displayed labels. A cell adjacent to the table does not belong to it.
  • The macro will not run: Excel for the web can open a workbook containing VBA but cannot run VBA macros. Use the desktop Excel application and allow macros only for files you trust.

For debugging, the Immediate window is useful because Debug.Print writes results without changing worksheet cells. You can also set a breakpoint and inspect pt.Name while stepping through the code. If the macro runs but prints nothing, check the workbook and worksheet references first, then test whether the collection’s count is greater than zero.

Run the VBA in Excel for Windows or Mac

In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the procedure into it. Run it from the editor or from Excel’s macro list. If the Developer tab is not visible, enable it in Excel Options > Customize Ribbon. The Immediate window is available in the Visual Basic Editor under View > Immediate Window.

Excel for Microsoft 365 on Mac also supports VBA in the desktop application. The Visual Basic Editor can be opened from the Developer tab; Option+F11 is the usual keyboard shortcut, though some Mac keyboards require Fn as well. Menu placement and shortcut behavior can vary with the Mac and Excel configuration. Excel for the web does not execute VBA, so use desktop Excel to run these examples. In all cases, save a copy before running a macro because VBA actions cannot be reversed with Ctrl+Z.

Frequently Asked Questions

How do I get a PivotTable name in Excel VBA?
Get the PivotTable object, then read its Name property. For example, with the worksheet stored in ws, use Set pt = ws.PivotTables(1) followed by Debug.Print pt.Name.

How do I list all PivotTable names in a workbook?
Loop through ThisWorkbook.Worksheets, then loop through each sheet’s PivotTables collection and print each pt.Name. Include ws.Name in the output to show where each one is located.

Can I get a PivotTable name from the selected cell?
Yes. Use ActiveCell.PivotTable to retrieve the PivotTable containing that cell, then read the returned object’s Name. Handle the case where the active cell is outside a PivotTable.

Is a PivotTable name the same as its worksheet name?
No. The worksheet name identifies the sheet; pt.Name identifies the PivotTable object on a worksheet. A PivotTable can have a different name from both its sheet and its source data.

Can VBA run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to execute the code.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.