Fix Excel VBA Error 1004 in a PivotTable and Power Query Macro
Question details
Identify the cause of VBA error 1004 in an Excel macro that uses a PivotTable and Power Query.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A macro accesses worksheets, a PivotTable, queries, tables, or ranges in a workbook.
- Observed behavior
- Excel reports application-defined or object-defined error 1004; the reported error line may be zero.
Open the workbook containing the macro and identify the procedure that fails. Check whether the macro is stored in that workbook or another workbook, because ThisWorkbook may not be the workbook containing the data.
Verify the workbook objects referenced by the macro
Use this check when a worksheet, PivotTable, field, query, table, or range may have been renamed or removed.
Inspect the worksheet tabs for Summary Dashboard and Helper Sheet. Compare their names with the VBA code exactly, including spaces and any leading or trailing spaces.
Click the PivotTable and open the PivotTable Analyze tab to check its name. Compare the field names in the PivotTable Fields pane with the filter fields referenced in the macro.
Open Data > Queries & Connections and compare the query and connection names with the code. If the macro references a worksheet table, click the table and check its name on the Table Design tab.
Open Formulas > Name Manager and confirm that each named range used by the macro exists, refers to the intended cells, and has the expected workbook or worksheet scope.
In the VBA editor, update unqualified references such as Worksheets or Range to identify the intended workbook and worksheet explicitly. Use ThisWorkbook only if the macro is stored in the workbook containing those objects.

Step through the macro and check event handling
Use the VBA debugger to find the failing statement, especially when the reported error line is zero.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Its Excel format support makes it easy to move everyday spreadsheet work between office suites. It does not fix Excel VBA error 1004, and VBA and Power Query compatibility may differ.

Frequently Asked Questions
What does VBA error 1004 mean in a PivotTable macro?
It is an application-defined or object-defined error that can occur for several reasons, including an invalid object reference. Check the failing statement and compare its PivotTable, field, worksheet, and range names with the workbook.
How do I find the name of my PivotTable?
Click anywhere inside the PivotTable, then open the PivotTable Analyze tab and check the PivotTable name shown there. Use that name when comparing it with the VBA reference.
Why does my error handler report line zero?
If the handler uses Erl, zero can mean that no numbered line was available to report. Open Developer > Visual Basic and press F8 to step through the procedure and locate the failing statement.
Where can I check the Power Query names used by my macro?
Open Data > Queries & Connections in Excel and compare the displayed query and connection names with the names in the VBA code. A query, connection, and worksheet table can have different names.
What if my worksheet change event keeps running again?
The event may be triggered by changes made by its own code. In the VBA editor, review the Worksheet_Change procedure and temporarily disable events while it makes changes; ensure error handling restores the previous Application.EnableEvents setting.




