Excel VBA: Get a PivotTable’s Location

VBA & macros · Updated November 21, 2026

To get a PivotTable’s location in Excel VBA, use its TableRange2 property and read the range’s address. For example, pt.TableRange2.Address(External:=True) returns the full report range, while pt.TableRange2.Cells(1, 1).Address returns its top-left cell.

What “location” means for a PivotTable

A PivotTable does not have one universal location value: the useful answer depends on what you need to do next. You might want the cell where the report begins, the complete rectangle occupied by the report, or the worksheet that contains it. A report can also include a report filter above the main grid, so deciding which part counts as its location matters.

For a PivotTable named PivotTable1 on a worksheet named Summary, the top-left cell might be A3, while the complete report could occupy A3:F18. These addresses change if the PivotTable is moved, resized, or refreshed with different results. VBA reads the current location at runtime rather than relying on an address that may later become stale.

The PivotTable object does not expose a general-purpose Location property. Instead, use properties that return ranges, then read details such as the address, row count, or worksheet from those ranges. This approach is more reliable than searching cells for a label such as “Row Labels,” because labels vary with layout, language, and field choices.

Use TableRange2 for the complete report

TableRange2 returns the range occupied by the PivotTable report, including report filter fields when they are displayed. It is usually the best choice when “location” means the full area to select, format, copy, clear, or inspect. The range’s Address property gives its cell coordinates; its Worksheet property identifies the sheet.

Use TableRange instead when you specifically want the PivotTable range excluding report filter fields. That distinction matters when a report filter appears above the main pivot grid: TableRange2 includes that area, while TableRange does not. In a PivotTable without report filter fields, the two ranges are typically the same.

Other range properties answer narrower questions. DataBodyRange refers to the values area, while RowRange and ColumnRange identify the row-field and column-field areas. PageRange refers to the report filter area. These are not interchangeable with the full PivotTable range: for example, copying only DataBodyRange will not include the report’s field headings.

VBA example: print the range and worksheet

This macro looks up a named PivotTable on a named worksheet, then prints its worksheet, full report address, and top-left cell in the VBA Immediate window. Replace the two sample names with the names in your workbook.

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

    ' Change these names to match your workbook.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")

    ' Print the current location details to the Immediate window.
    Debug.Print "PivotTable: " & pt.Name
    Debug.Print "Worksheet: " & ws.Name
    Debug.Print "Full range: " & pt.TableRange2.Address(External:=True)
    Debug.Print "Top-left cell: " & _
        pt.TableRange2.Cells(1, 1).Address(External:=True)
End Sub

For example, if the report occupies cells A3:F18 on the Summary sheet, the output identifies Summary as the worksheet, gives the full range as an external address such as [Sales.xlsx]Summary!$A$3:$F$18, and reports the top-left cell as [Sales.xlsx]Summary!$A$3. Excel may put apostrophes around a sheet name when its name requires them. The exact workbook name and coordinates depend on the open file.

On Windows, open the Visual Basic Editor with Alt+F11, then show the Immediate window with Ctrl+G. Run the procedure with the cursor inside it or from the Macro dialog. Save a copy of the workbook before running a macro: VBA macros cannot be undone with Ctrl+Z. This example only prints information, but the precaution is useful for VBA generally.

Find the PivotTable and its name

The code needs both a worksheet name and a PivotTable name because each worksheet has its own PivotTables collection. To identify them, select a cell in the PivotTable in Excel and look for its name in the PivotTable Analyze tab’s PivotTable Name box. The sheet name appears on the worksheet tab, though the tab can be renamed independently of the PivotTable.

If you do not know which sheet contains the report, loop through worksheets and their PivotTables. This version prints each PivotTable’s name, sheet, and full current range, which is useful in workbooks with several reports:

Sub ListPivotTableLocations()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Use the workbook that is currently active in Excel.
    Set wb = ActiveWorkbook

    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            Debug.Print pt.Name & " | " & ws.Name & " | " & _
                pt.TableRange2.Address(External:=True)
        Next pt
    Next ws
End Sub

Notice the workbook choice in these examples. ThisWorkbook means the workbook that contains the VBA code. ActiveWorkbook means the workbook currently active in Excel. If code is stored in the Personal Macro Workbook or an add-in, ThisWorkbook may not be the file containing the PivotTable. In that situation, use the intended workbook explicitly or use ActiveWorkbook only when you are certain it is the target.

Choose the right range property

Use the property that matches the task rather than assuming every range describes the same part of the report:

  • TableRange2: the entire displayed PivotTable report, including report filter fields; use it for the broadest location.
  • TableRange: the report range without report filter fields; useful when the filter area should be left out.
  • DataBodyRange: the PivotTable’s data area, not the full report with its headings.
  • RowRange and ColumnRange: the areas associated with row fields and column fields.
  • PageRange: the report filter area, when one is present.

For a simple “where does this PivotTable start?” answer, the first cell of TableRange2 is a straightforward choice. For selecting all cells in the report, keep the complete range object rather than using only its address string. An address is text; a Range object can be passed to other VBA operations, such as formatting or copying.

Use the location in other VBA tasks

You can store the range in a variable and use it directly. For instance, a procedure that applies a border to the complete report can work with pt.TableRange2, while a procedure that exports coordinates can write pt.TableRange2.Address to a worksheet cell. To get the worksheet name independently, use pt.TableRange2.Worksheet.Name. This is handy when the PivotTable object was found by looping through several sheets.

If another procedure needs only the starting row and column numbers, use the first cell of the chosen range and read its Row and Column properties. Those numbers are often more convenient than parsing an address string. For example, a report starting at cell D7 has row 7 and column 4. This remains reliable when the PivotTable moves, unlike hard-coded numbers that assume a fixed layout.

Keep in mind that the full report can change size after a refresh. A new source item may add a row or column, and a changed layout can move headings or filters. Retrieve the range after refreshing if the macro needs the latest dimensions. A saved address from before the refresh may no longer describe the current report.

Common problems and how to avoid them

A “Subscript out of range” error often means the worksheet name is wrong, or the named worksheet is not in the workbook referenced by the code. A similar lookup failure can occur if the PivotTable name is mistyped or if it belongs to a different worksheet. Check spelling, spaces, and the workbook reference; worksheet tabs and PivotTable names are separate identifiers.

If Excel reports that it cannot get the PivotTables item, first confirm that the target sheet actually contains a PivotTable. A normal cell range or an Excel table is not itself a PivotTable. Also make sure the selected workbook is the one intended. When the macro is in an add-in, changing from ThisWorkbook to a deliberate target workbook is often the key fix.

Another common surprise is getting a larger range than expected. That usually happens because the report uses filter fields and the code asks for TableRange2. If the intended answer is the main body without those filters, use TableRange and verify the result against the displayed report. Conversely, do not switch to DataBodyRange when you need headers or the whole report.

Finally, PivotTable coordinates are not a permanent identifier. Moving a PivotTable or refreshing it can change its location or dimensions. If your macro will run repeatedly, look up the PivotTable by its name and calculate its range at the time of the operation rather than saving a cell address in the code.

Excel version and platform notes

These PivotTable object-model properties are used in desktop Excel VBA, including Microsoft 365 for Windows. Excel for the web can display a workbook that contains macros, but it cannot run VBA; open the file in desktop Excel to execute the procedures. VBA’s editor and ribbon layout differ on Mac, but the range-based approach is the same in desktop Excel for Mac where VBA is available. The Windows shortcuts listed above may not match Mac keyboards, so use the Visual Basic Editor and its Immediate window through the Mac interface.

Also distinguish the address returned by VBA from the PivotTable’s source data location. TableRange2.Address describes where the report is displayed, not where its source records live. A PivotTable can use a worksheet range, an Excel table, an external connection, or a data model as its source. To inspect or change source information, use the PivotTable’s source or connection settings; its displayed range address does not reveal that source.

Frequently Asked Questions

How do I get the top-left cell of a PivotTable in VBA?
Use pt.TableRange2.Cells(1, 1).Address. It returns the address of the first cell in the complete report range. Add External:=True to include workbook and worksheet information in the address.

What is the difference between TableRange and TableRange2?
TableRange excludes report filter fields, while TableRange2 includes them. If the PivotTable has no report filter fields, they generally describe the same displayed area. Choose based on whether those filters should count as part of the location.

How can I get the worksheet name for a PivotTable?
If you already have the PivotTable object, use pt.TableRange2.Worksheet.Name. If you already have its worksheet object, use ws.Name. The worksheet name and PivotTable name are distinct and should not be confused.

Can I get the location without knowing the PivotTable name?
Yes. Loop through each worksheet’s PivotTables collection, as in the listing example, and print or inspect every object’s name and range. If you want one particular report afterward, use its name to identify it among those results.

Does this return the PivotTable’s source-data range?
No. It returns the report’s displayed range on the worksheet. A PivotTable’s source may be elsewhere or may come from an external connection or data model, so inspect its source settings separately when that is what you need.

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.