Excel Error Codes

Fix Excel VBA Runtime Error 1004 in a WPS Spreadsheets PivotTable Macro

Bushra Parveen Nov 13, 2026 869 views

Question details

The user needs to fix Excel VBA Runtime Error 1004, which reports that the PivotTable field name is not valid when a macro creates a PivotTable from ActiveSheet.UsedRange in WPS Spreadsheets.

Fix Excel VBA Runtime Error 1004 in a WPS Spreadsheets PivotTable Macro

Product: WPS Spreadsheets / WPS Office
Device & OS: not provided
Scenario: A recorded or existing VBA macro tries to create a PivotTable from ActiveSheet.UsedRange, but the field name is rejected.
Observed behavior: The macro displays Runtime Error 1004: The PivotTable field name is not valid and stops before creating the PivotTable.

Before you start

Before you edit the macro, open the workbook in WPS Spreadsheets and confirm that the used range has a header row with unique, nonblank field names. Also note the exact sheet name and the PivotTable field names you want to add.

Solution 1Recommended

Rewrite the recorded PivotTable macro to reference objects directly

Use this solution when a recorded macro returns Error 1004 because Select, Selection, or ActiveCell causes an invalid PivotTable field name.

Recorded PivotTable macros often rely on Select and Selection. When the active cell is not inside a valid header row, the PivotTable cache receives a blank or invalid field name and raises Runtime Error 1004.

Referencing ActiveSheet.UsedRange and the PivotTable object directly removes the dependency on the current selection and makes the macro work consistently.

Open the VBA editor

In WPS Spreadsheets, press Alt+F11 or go to Developer > Visual Basic to open the Microsoft Visual Basic for Applications editor.

Locate the recorded macro

In the Project Explorer, expand VBAProject > Modules and double-click the module that contains the PivotTable macro.

Replace selection-based code

Delete statements that use Select, Selection, or ActiveCell, and refer directly to the worksheet and range object instead.

Create the PivotTable from the used range

Use Dim pt As PivotTable and Set pt = ActiveWorkbook.PivotCaches.Create(xlDatabase, ActiveSheet.UsedRange).CreatePivotTable with an empty PivotTable name argument.

Add fields by validated header name

Add each field with the exact header text, for example Region as a row field and Sales as a data field, so the field name matches a valid source header.

Rewrite the recorded PivotTable macro to reference objects directly

Error 1004 cleared: After the macro runs without Select or ActiveCell selection, the PivotTable should be created from the used range without the field name error.

Solution 2

Validate source data headers before creating the PivotTable

Use this when the source range may have blank, missing, or duplicate column headers that Excel treats as invalid PivotTable field names.

WPS Spreadsheets VBA fix

Fix and run the PivotTable macro in WPS Spreadsheets

Use WPS Spreadsheets to open the Excel macro workbook, edit the VBA code in the built-in editor, validate the source range, and create the PivotTable without Runtime Error 1004.

1. Download and install WPS Office: Go to the official WPS Office site, download the free installer, and open WPS Spreadsheets.
2. Open the macro-enabled workbook: Click Menu > Open, select the .xlsm or .xls Excel file, and enable macros if prompted.
3. Open the VBA editor: Press Alt+F11 or go to Developer > Visual Basic to view and edit the PivotTable macro.
4. Modify and run the macro: Replace Select/Selection/ActiveCell with direct object references, validate the source headers, and click Run Macro or press F5 to execute the fixed code.
5. Refresh and verify the PivotTable: After the PivotTable is created, check the field list and refresh the PivotTable with Data > Refresh All to confirm it updates correctly.

Direct VBA editing and PivotTable creation in WPS SpreadsheetsFull compatibility with .xls, .xlsx, and .xlsm macro-enabled Excel filesFree, lightweight Office suite with a familiar ribbon interfaceNo need to rewrite the workbook in another format before fixing the macro

microsoft office alternative - wps office

Frequently Asked Questions

Why does a recorded PivotTable macro throw Runtime Error 1004 with the message that the PivotTable field name is not valid?

Recorded macros often use Select, Selection, or ActiveCell. When the current selection or used range does not contain a valid header row, the macro passes a blank or invalid field name to the PivotTable. Open the VBA editor with Alt+F11 and replace selection-based code with direct references to ActiveSheet.UsedRange.

Can I omit the PivotTable name when creating a PivotTable in WPS Spreadsheets VBA?

Yes. The PivotTable name is optional. Use an empty PivotTable name argument with CreatePivotTable and assign the returned PivotTable to an object variable; WPS Spreadsheets names it automatically.

How do I check whether my source range has valid PivotTable field headers?

Select the data range and confirm that the first row contains unique, nonblank labels for every column. Remove completely blank columns or duplicate header cells before rerunning the macro.

Does WPS Office support Excel VBA macros and PivotTables?

WPS Spreadsheets can open and run Excel VBA-enabled workbooks and create PivotTables. In WPS Office, press Alt+F11 to open the VBA editor and edit the macro code directly.

What should I do if the macro still shows Error 1004 after rewriting?

Make sure the PivotCache is created with xlDatabase and ActiveSheet.UsedRange, and add only field names that exactly match the source headers. In the VBA editor, press F8 to step through the macro and locate the line that still produces the error.

Microsoft Office Error Codes