Use Excel VBA to Process Input Rows and Save Analysis Results in WPS Office
Question details
The user wants to automate a process that reads input rows from one workbook, copies the values into an analysis workbook, recalculates a table, and saves a separate summary sheet or workbook for each iteration.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building an Excel VBA macro that loops through rows in an All_Inputs table, sends four input values to an analysis workbook, recalculates a Full_A table, and saves each summary result separately.
- Observed behavior
- The user needs a repeatable VBA routine because manually copying each input row, recalculating, and saving every summary iteration is time-consuming.
Before writing the macro, confirm that WPS Office Spreadsheets can open both the input and analysis workbooks and that the Developer tab or macro features are available. Identify the exact input cells, the output table location, and the folder path where each summary workbook should be saved.
Write a VBA Loop to Process Each Input Row and Save the Summary Workbook
Use this solution when you want a single repeatable macro that copies each input row into the analysis workbook, recalculates the Full_A table, and exports one summary workbook per row.
This approach assumes the input table is named All_Inputs and that the analysis workbook already contains the input cells, Full_A table, and Summary sheet. Direct value assignment is preferred over Select or Activate statements.
Open the workbook that holds the input rows in WPS Office Spreadsheets. Click the Developer tab and choose Visual Basic, or go to Tools > Macro > Visual Basic Editor, to open the VBA editor.
In the VBA editor Project Explorer, right-click the active workbook and select Insert > Module. Declare the source worksheet, analysis workbook, output workbook, and a loop variable so the macro can reuse the same objects for every row.
Create a For Each loop over the All_Inputs table rows. For each row, write the four input values into the analysis workbook input cells, using direct value assignment instead of Select or Activate.
Inside the loop, recalculate the Full_A table with Application.Calculate or the specific sheet Calculate method. Then copy the Summary sheet to a new workbook so it becomes the active output workbook.
Use SaveAs to save the active output workbook into a dedicated folder with a unique name such as Summary_1.xlsx for the first row, incrementing the number on each loop. Close the temporary output workbook before continuing.
Record One Export and Add a Loop Around It
Use this method when you want WPS Office to capture the copy, recalculate, and save actions before you manually add the loop in VBA.
Automate Input-Row Analysis Exports with WPS Office Spreadsheets
You can build the same VBA automation in WPS Office Spreadsheets: open the VBA editor, loop through the All_Inputs rows, copy each input set into the analysis workbook, recalculate the Full_A table, and save each Summary sheet as a separate workbook. WPS Office preserves Excel VBA macro structures, so your existing code can often run with minimal adjustment.
- 1. Open WPS Office Spreadsheets and access VBA: Open your input workbook in WPS Office Spreadsheets, click the Developer tab, and choose Visual Basic, or go to Tools > Macro > Visual Basic Editor.
- 2. Paste or write the loop macro: Create a VBA macro that loops through the All_Inputs table, copies the four values into the analysis workbook, and recalculates the Full_A table.
- 3. Export each summary worksheet: Inside the loop, copy the Summary sheet to a new workbook and call SaveAs with a unique name such as Summary_1.xlsx, Summary_2.xlsx, and so on.
- 4. Run and verify outputs: Run the macro and open the output folder to confirm each input row has its own summary workbook.
Frequently Asked Questions
Does WPS Office support Excel VBA macros that loop through rows and save workbooks?
Yes. WPS Office Spreadsheets supports VBA macros and can open macro-enabled Excel workbooks such as .xlsm files. You can write or edit the loop in the VBA editor, but test any external references or ActiveX controls for compatibility.
How do I make the macro run faster when processing many input rows?
Inside the loop, disable screen updating with Application.ScreenUpdating = False, set calculation to manual if it is stable, and avoid Select or Activate statements. Restore screen updating and calculation after the loop.
How can I save each summary without overwriting the previous workbook?
Build the file name from the loop counter or a row identifier, such as Summary_ followed by the iteration number and .xlsx. Use SaveAs with a valid output folder path and close the workbook before the next iteration.
Where can I find the VBA editor in WPS Office Spreadsheets?
You can open it from the Developer tab by choosing Visual Basic, or go to Tools > Macro > Visual Basic Editor. If the Developer tab is not visible, enable it through the ribbon customization options.
Can the macro recorder help me build this process if I am not familiar with VBA?
Yes. Record one manual export with WPS Office, stop the recorder, open the VBA editor, and wrap the recorded actions in a For Each loop over the All_Inputs rows. Then replace fixed output names with a unique name per row.
