VBA & Macro Problems

Fix an Excel PivotTable Macro Using the Wrong Worksheet

Amos GikundaAmos Gikunda Nov 14, 2026 868 views

Question details

The user needs an Excel VBA macro to reference the worksheet containing a PivotTable instead of relying on the active worksheet.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro accesses a PivotTable field while a different worksheet may be active.
Observed behavior
The macro fails when it searches the active worksheet and the specified PivotTable is on another sheet.
Before you start

Confirm the worksheet name, PivotTable name, and field name in Excel. Select the PivotTable and check its name on the PivotTable Analyze tab before editing the macro.

Solution 1Recommended

Reference the PivotTable worksheet explicitly in VBA

Use this fix when the macro may run while a worksheet other than the PivotTable's sheet is active.

ActiveSheet refers to whichever sheet is currently selected. ThisWorkbook refers to the workbook containing the VBA code, so use it when that workbook also contains the PivotTable.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic, then locate the module containing the macro.

2
Replace the sheet reference

Find the line using ActiveSheet.PivotTables and replace it with an explicit reference, for example: Set pf = ThisWorkbook.Worksheets("SheetName").PivotTables("PivotTable5").PivotFields("Month").

3
Enter the actual names

Replace SheetName, PivotTable5, and Month with the worksheet, PivotTable, and field names used in your workbook.

4
Test the macro

Return to Excel, select a different worksheet, and run the macro from Developer > Macros to confirm it can find the PivotTable.

If the macro is stored elsewhere: If the PivotTable is in a different open workbook from the VBA code, use an explicit reference such as Workbooks("Report.xlsx").Worksheets("SheetName") instead of ThisWorkbook.Worksheets("SheetName").
Free Microsoft Office alternative

Work with PivotTable spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Its Excel format compatibility helps you move existing files between office suites. Use Excel to apply and test this VBA fix; macro support in WPS can vary by version and edition.

Open and work with Microsoft Excel XLSX filesReview spreadsheet data and PivotTables in a familiar interfaceUse a free, lightweight office suite for everyday document workMove compatible files between WPS Office and Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Why does ActiveSheet.PivotTables fail on another worksheet?

ActiveSheet.PivotTables searches only the currently selected sheet. If the PivotTable is elsewhere, reference it through ThisWorkbook.Worksheets("SheetName").PivotTables("PivotTable5").

Where can I find the PivotTable name?

Click a cell inside the PivotTable, then open the PivotTable Analyze tab and check the PivotTable Name field. Use that exact name in the VBA reference.

What if the macro is stored in a different workbook?

ThisWorkbook refers to the workbook containing the VBA code. If the PivotTable is in another open workbook, reference it explicitly with Workbooks("Report.xlsx").Worksheets("SheetName").

Why does the macro still fail after I specify the worksheet?

Check that the worksheet, PivotTable, and field names in the code match the workbook. Select the PivotTable and check its name on the PivotTable Analyze tab, then verify that the requested field is present.