VBA or Power Query to Iterate Excel Inputs and Save Results in WPS Spreadsheets
Question details
The user needs to automate a repeated WPS Spreadsheets workflow that copies each input row into a calculation table, waits for formula results, appends the required output rows to a summary table, and removes rows where Result 2 equals No.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Iterative Excel calculation workflow
- Observed behavior
- Manual copying and filtering is repetitive, and the user wants VBA or Power Query to replay the process automatically.
Open the workbook in WPS Spreadsheets and make sure the Developer tab is enabled. If you plan to save the macro, use a macro-enabled workbook format such as .xlsm.
Use VBA to Loop Through Input Rows, Run Calculations, and Save Valid Results
Use this when the calculation must be repeated row by row and the results depend on live worksheet recalculation.
VBA can clear the analysis table, copy each input row, wait for calculation, and append only the rows that meet your condition. Unlike Power Query, VBA keeps the live worksheet calculation engine in the loop.
In WPS Spreadsheets, click the Developer tab, select Visual Basic, or press Alt+F11. Then insert a module from Insert > Module.
Paste a macro that loops through the Inputs table rows, clears the analysis table, copies the current input row into the calculation table, and uses Application.Calculate or DoEvents to wait for results.
Inside the loop, read the relevant analysis rows, copy them to Summaries, and delete rows where Result 2 equals No, for example by checking If .Range("Result2").Value = "No" Then .Row.Delete.
Return to the Developer tab, click Macros, select the macro, and click Run. Confirm that the summary table contains only the valid output rows.
Use Power Query for Transform-and-Filter Workflows Only
Use this if the result can be created through query transformations and does not require row-by-row waiting on worksheet formulas.
Automate Iterative Excel Input and Save Results with WPS Spreadsheets VBA
Run the VBA loop directly in WPS Spreadsheets, keep workbook compatibility with .xlsm and .xlsx files, and avoid manually copying each input row.
- 1. Download and install WPS Office: Go to wps.com and download WPS Office, then open your macro-enabled workbook in WPS Spreadsheets.
- 2. Enable the Developer tab: Click File > Options > Customize Ribbon, check Developer, and click OK.
- 3. Open the VBA editor: Click Developer > Visual Basic or press Alt+F11 to open the macro editor.
- 4. Paste and run the macro: Insert a module, paste your VBA loop, then click Developer > Macros > Run.
- 5. Save valid results: Save the workbook as .xlsm to keep the macro and the completed summaries.
Frequently Asked Questions
Does WPS Spreadsheets support VBA for iterative calculations?
Yes. Enable the Developer tab, open the Visual Basic editor, and save the workbook as .xlsm so the macro remains available.
How do I enable the Developer tab in WPS Spreadsheets?
Click File > Options > Customize Ribbon, check Developer, and then click OK. The Developer tab will appear in the ribbon.
Can Power Query wait for worksheet formulas to recalculate?
No. Power Query is an ETL tool and does not interact with worksheet calculation timing. Use VBA when the workflow requires waiting for results before continuing.
How do I delete rows where Result 2 equals No in VBA?
Inside the loop or after appending rows, test the value with If .Range("Result2").Value = "No" Then .Row.Delete, or filter the column and delete visible rows.
Why is my macro not saved in WPS Spreadsheets?
You likely saved the workbook as .xlsx. Macros are removed from standard .xlsx files, so save the file as a macro-enabled workbook with the .xlsm extension.
