VBA & Macro Problems

Fix Excel VBA Error 1004 in a PivotTable and Power Query Macro

Olivia MillerOlivia Miller Nov 20, 2026 869 views

Question details

Identify the cause of VBA error 1004 in an Excel macro that uses a PivotTable and Power Query.

Fix Excel VBA Error 1004 in a PivotTable and Power Query Macro
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.
Before you start

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.

Solution 1Recommended

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.

1
Check worksheet names

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.

2
Check the PivotTable and fields

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.

3
Inspect queries and tables

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.

4
Verify named ranges

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.

5
Qualify references

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.

Verify the workbook objects referenced by the macro
Free Microsoft Office alternative

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.

Open and edit common Microsoft Excel formats, including XLSX and XLSWork with spreadsheet data and PivotTables in a familiar interfaceUse a free, lightweight office suite for everyday spreadsheet tasksMove compatible spreadsheet files between WPS Office and Microsoft Office
microsoft office alternative - wps office

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.