VBA Select Pivot Table: Safe Methods and Examples

VBA & macros · Updated November 18, 2026

To select an entire PivotTable with VBA, use its TableRange2 property and call Select: Worksheets("Report").PivotTables("PivotTable1").TableRange2.Select. The worksheet must be active before a range can be selected; for most automation, it is better to work with the PivotTable object directly and avoid selecting cells.

What “select a PivotTable” means in VBA

In Excel, selecting a PivotTable usually means selecting the worksheet cells occupied by it. VBA represents the PivotTable itself as a PivotTable object, while the cells it occupies are exposed through range properties. That distinction matters: you can use an object to refresh, filter, or inspect a PivotTable without changing the user’s current cell selection.

There is no need to click the PivotTable manually before a macro can find it. A workbook, worksheet, and PivotTable name identify it directly. Names such as PivotTable1 are common defaults, but they may have been changed, so check the actual name in Excel’s PivotTable Analyze tab or in the VBA editor’s object browser.

Select the whole PivotTable with TableRange2

TableRange2 returns the full range occupied by the PivotTable, including report-filter fields when present. To select that range, first activate the worksheet that contains the PivotTable. This example assumes the macro is stored in the workbook containing the sheet named Report:

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

    ' Identify the worksheet and PivotTable by name.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set pt = ws.PivotTables("PivotTable1")

    ' A range can be selected only on the active worksheet.
    ws.Activate
    pt.TableRange2.Select
End Sub

Replace both names with the ones in your file. If the macro is stored in a different workbook, ThisWorkbook refers to the workbook containing the code, not necessarily the workbook with the PivotTable. In that situation, qualify the correct workbook explicitly or use ActiveWorkbook only when you are certain the intended workbook is active.

Before running a macro, save a copy of the workbook. VBA actions cannot be reversed with Ctrl+Z, and a selection macro can be part of a larger procedure that changes data or PivotTable settings.

Choose the right PivotTable range

The best range property depends on what you mean by “the PivotTable.” These properties are not interchangeable:

  • TableRange2 covers the entire PivotTable, including report-filter fields. Use it when you want to highlight or copy the complete displayed report.
  • TableRange1 covers the PivotTable but excludes report-filter fields. Use it when the filters above the report should remain outside the selection.
  • DataBodyRange refers to the PivotTable’s data area, rather than its row and column headings. It is useful when the task concerns values only.

For example, a report with a Region report filter, product names down the rows, months across the columns, and sales totals in the body has different useful selections. TableRange2 includes the Region filter; TableRange1 leaves it out; DataBodyRange targets the value cells. A PivotTable’s size can change after refresh or filtering, so these properties are safer than hard-coded addresses such as A3:F18.

Select only the values in the PivotTable

To select the data body instead of the full report, use DataBodyRange. A PivotTable with no displayed data may not provide a usable data-body range, so check for Nothing before selecting it:

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

    ' Find the PivotTable on the specified sheet.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set pt = ws.PivotTables("PivotTable1")

    ' Activate the sheet before selecting its range.
    ws.Activate
    If Not pt.DataBodyRange Is Nothing Then
        pt.DataBodyRange.Select
    End If
End Sub

This range is not the same as “all numeric-looking cells.” It represents the PivotTable’s data area, whose contents can include calculated values or other displayed results. If you need to process values, reading them from the range or using PivotTable-related objects is often more dependable than relying on the selection being visible.

Work with the PivotTable without selecting it

Selection is mainly useful when a macro needs to show a user where it is working, or when the next action is inherently selection-based. Many routine tasks do not need it. For instance, a PivotTable can be refreshed through its object, without activating its worksheet:

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

    ' Set references directly; no cell selection is required.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set pt = ws.PivotTables("PivotTable1")

    pt.RefreshTable
End Sub

Similarly, you can inspect properties, change supported settings, or refer to a range through the PivotTable object. Avoiding selection reduces screen flicker and prevents a macro from unexpectedly moving the user’s active cell. It also makes code less sensitive to which sheet happened to be active when the procedure began.

If a later line really does require a selected range, select it at that point and qualify the worksheet and PivotTable first. Do not rely on Selection to identify the report: it may refer to a completely different cell or object if the user clicked elsewhere.

Find the correct worksheet and PivotTable name

VBA needs the worksheet’s tab name and the PivotTable’s name, not the data source name. To inspect the PivotTable name, click inside it and look on the PivotTable Analyze tab; the PivotTable Name box is near the left side of the ribbon. The name can also be checked in the Immediate window in the VBA editor, for example by examining the Name property of a PivotTable in the relevant worksheet’s PivotTables collection.

Excel can contain several PivotTables on one sheet, and the same PivotTable name can exist on different sheets. Therefore, use both the worksheet and PivotTable names to identify the intended report. If a name is misspelled or the specified worksheet does not exist, VBA raises an error rather than selecting a different PivotTable automatically.

For code that will be reused across files, consider checking that the worksheet and PivotTable exist before attempting to use them. A missing object should produce a clear message or a controlled exit, rather than leaving a confusing run-time error for the person using the workbook.

Common selection errors and how to fix them

  • “Select method of Range class failed.” The PivotTable’s worksheet is probably not active. Activate the worksheet before calling Select, or avoid selection and work with the range object directly.
  • “Subscript out of range.” Check the worksheet tab name, workbook reference, and PivotTable name. Names must match, including spaces.
  • The wrong PivotTable is selected. Do not use an unqualified expression such as ActiveSheet.PivotTables(1) unless the active sheet and collection order are deliberately controlled. Identify the sheet and PivotTable by name.
  • The selected cells omit a filter or include too much. Choose between TableRange1 and TableRange2; use DataBodyRange for the values area.
  • The range changes after refresh. This is expected when the report gains or loses rows or columns. Use the PivotTable’s range properties rather than storing a fixed address.

A further source of confusion is the workbook reference. ThisWorkbook means the workbook where the VBA code is saved. ActiveWorkbook means the workbook currently active in Excel. When those are different—for example, when a macro is run from a personal macro workbook—the two expressions point to different files.

Windows, Mac, and Excel for the web

The VBA object model and the basic PivotTable range properties are available in desktop Excel for Windows and Mac. The VBA editor and some automation features differ between platforms, but the central pattern remains the same: identify the worksheet and PivotTable, then select the desired range only if selection is necessary. On Windows, the VBA editor is commonly opened with Alt+F11; on Mac, use Developer > Visual Basic, or the corresponding shortcut available for the keyboard and Excel configuration.

Excel for the web does not run VBA macros. You can open a macro-enabled workbook there, but to execute or edit VBA, use the desktop Excel application. If a workbook must automate in a browser, Office Scripts may be an alternative, but they use a different programming model and are not a direct VBA translation.

Frequently Asked Questions

How do I select a PivotTable by name in VBA?
Reference it through its worksheet’s PivotTables collection, such as Worksheets("Report").PivotTables("PivotTable1"), then select TableRange2 after activating the worksheet.

What is the difference between TableRange1 and TableRange2?
TableRange1 excludes report-filter fields, while TableRange2 includes them. Choose based on whether those filter cells should be part of the selection.

How do I select only PivotTable values?
Use PivotTable.DataBodyRange.Select after activating the relevant sheet. Check that DataBodyRange is not Nothing before selecting it.

Why does VBA say that the Select method failed?
Most often, the range belongs to a worksheet that is not active. Activate its worksheet first, or avoid selecting and operate on the range or PivotTable object directly.

Can VBA select a PivotTable in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel to run the macro; an Office Script may suit some browser-based automation needs.

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.