Use WPS Spreadsheets VBA to Copy Changed Rows and Clear Input Cells
Question details
The user wants to automate copying selected row values to another worksheet when a drop-down changes, then clear the original input cells.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is maintaining a log workbook and wants a monitored cell change to move data to a history sheet without manual copy-paste.
- Observed behavior
- When a drop-down changes to Option 2, the specified cells should be copied to the next available row on Sheet 2, and the input cells should be cleared on Sheet 1.
Open WPS Spreadsheets, enable the Developer tab, and save the workbook as a macro-enabled file (.xlsm). Press Alt+F11 to confirm that the VBA editor opens and that the Sheet1 code module is available.
Create a Worksheet_Change Event Macro in WPS Spreadsheets
Best for automatically copying changed rows and clearing input cells whenever a drop-down value changes.
A Worksheet_Change procedure is required because formulas alone cannot move values or maintain a historical log.
In WPS Spreadsheets, click Developer > Visual Basic or press Alt+F11, then double-click Sheet1 in the Project Explorer.
Select Worksheet from the Object drop-down and Change from the Procedure drop-down to insert the Private Sub Worksheet_Change(ByVal Target As Range) procedure.
Inside the event, check if the changed cell is the monitored drop-down cell, and when the value is Option 2, copy cells such as A, D, E, and F to the next empty row on Sheet2, then clear cells D and E on Sheet1.
Save the workbook as a macro-enabled file (.xlsm), change the drop-down to Option 2, and confirm that the row appears on Sheet2 and the input cells are cleared.
Record a Macro and Adapt the Generated VBA for All 13 Rows
Useful for beginners who want a starting point for the copy-paste and clear actions before editing the VBA.
Automate Row Logging with WPS Spreadsheets VBA
Use WPS Spreadsheets VBA to run a Worksheet_Change macro that copies changed row data to a log sheet and clears the input cells automatically.
- 1. Enable the Developer tab: Open WPS Spreadsheets, click the File menu or Options, and enable the Developer tab on the ribbon.
- 2. Open the VBA editor: Click Developer > Visual Basic or press Alt+F11, then locate Sheet1 in the Project Explorer.
- 3. Paste the Worksheet_Change code: Double-click Sheet1 and paste the event code that checks the drop-down cell, copies the specified row cells to Sheet2, and clears the input cells.
- 4. Save as macro-enabled workbook: Save the file as Excel Macro-Enabled Workbook (*.xlsm) so the VBA code remains available.
- 5. Test with the drop-down: Change the monitored cell to Option 2 and verify that the row is added to the log sheet and the original inputs are cleared.
Frequently Asked Questions
Does WPS Spreadsheets support Excel VBA macros like Worksheet_Change?
Yes. On Windows, WPS Spreadsheets supports VBA macros, including worksheet events. You can open the editor with Developer > Visual Basic or Alt+F11 and save the workbook as a .xlsm file.
Can I use formulas instead of VBA to move changed rows to another sheet?
No. Formulas can calculate or display values but cannot move cell values to another sheet or keep a running historical log. Use a Worksheet_Change event in VBA.
Where should the Worksheet_Change code be placed?
Place it in the Sheet1 code module, not a standard module. In the VBA editor, double-click Sheet1 in Project Explorer, then select Worksheet and Change to create the event.
What should I do if the macro does not run when the drop-down changes?
Check that macros are enabled, the code is in the worksheet module, events are enabled, and the file is saved as .xlsm. Also verify that the monitored range matches the Target cell you are testing.
How do I clear the original input cells after copying?
Inside the Worksheet_Change event, after copying the destination row, clear the original input cells with the ClearContents method for the affected row.
