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.Nameto identify the PivotTable in VBA or refer to it through a worksheet’s PivotTables collection. - Worksheet name: use
ws.Nameto 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
ThisWorkbookorActiveWorkbook. - “Subscript out of range” for a PivotTable: the worksheet may have no PivotTables, or the name or index is incorrect. Check
ws.PivotTables.Countbefore 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
- Excel Split One Cell Into Multiple Rows: How It Works
- Excel Split Print Area Into Two Pages: Page Breaks
- Excel Split Contents of a Cell Into Two Cells
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Code to Remove an Excel Workbook Password SafelyVBA can remove a password from Excel worksheet protection or workbook-structure protection when you already know the password;…
- Excel VBA Range Hyperlinks: Insert, Edit, and RemoveTo insert a hyperlink into an Excel range with VBA, use the worksheet’s Hyperlinks.Add method and set its…
- Excel VBA UserForm Coding: Build a Data-Entry FormExcel VBA UserForm coding means creating a custom dialog in Visual Basic for Applications and writing code that…
- MS Access VBA Dictionary: Setup, Examples, and TipsYes. Microsoft Access VBA can use the Scripting.Dictionary object to store key-value pairs, look up a value by…
- Excel VBA: Initialize Arrays, Set Values, and Resize ThemTo initialize an array in Excel VBA, declare it and assign values: use Dim items As Variant: items…
- VBA Dictionary For Each: Loop Through Keys and ItemsIn VBA, use For Each key In dict.Keys to loop through a Scripting.Dictionary’s keys, then read each value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.