How to Automatically Move DONE Rows and Sort Work Orders with VBA in Spreadsheets
Question details
The user needs a VBA macro script that automatically triggers when a row's status is changed to 'DONE', moving that row to another location, sorting the remaining work orders numerically, and preserving all original cell formatting.

- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing dynamic task lists or work orders where completed items must be archived to a separate sheet and active items must be instantly reorganized.
- Observed behavior
- The sorting and moving process is currently manual, consuming time and risking formatting breakage. The desired state is a fully automated background process driven by status changes.
Identify the exact column index that contains your 'DONE' status and note the exact name of the destination worksheet. Ensure that neither the source nor the destination data ranges contain merged cells, as they will cause the VBA copy-paste operation to fail.
Implement a Worksheet_Change Event Macro
This solution uses a built-in event handler that constantly monitors your sheet for changes, moving the row and sorting the remaining data the moment 'DONE' is entered.
The Worksheet_Change event is specific to a single sheet rather than the whole workbook. Because the macro will be deleting and modifying rows, it is crucial to temporarily disable Excel/WPS events during execution to prevent the macro from triggering itself into an infinite loop.
Right-click the sheet tab containing your active work orders at the bottom of the screen and select 'View Code'. This opens the VBA Editor directly to the specific module where the event macro must reside.
Create a `Private Sub Worksheet_Change(ByVal Target As Range)`. Inside the sub, immediately add `Application.EnableEvents = False` so the upcoming row deletion doesn't re-trigger the macro.
Write an `If` statement to check if the modified cell (Target) is in your status column (e.g., `Target.Column = 5`) and if its value is 'DONE'.
Use `Target.EntireRow.Copy` and specify the destination by finding the first empty row on your 'Completed' sheet (e.g., `Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)`). Standard VBA copying automatically preserves cell formatting.
Add `Target.EntireRow.Delete` to remove the item from the active sheet. Next, call the `Range.Sort` method on your work-order range, specifying the work-order column as `Key1` and `xlAscending` as the order.
Add `Application.EnableEvents = True` at the end of the script to resume normal monitoring. Finally, save your document via File > Save As and choose the 'Macro-Enabled Workbook (*.xlsm)' format.

Automate Your Workflows seamlessly with WPS Spreadsheet
WPS Office provides robust and stable support for VBA macros, allowing you to easily run Worksheet_Change events, manage task lists, and automatically sort rows without performance drops.
- 1. Enable Developer Tools: Open your workbook in WPS Spreadsheet, navigate to the 'Developer' tab, and ensure macros are enabled in your security settings.
- 2. Open the VB Editor: Click the 'VB Editor' button on the ribbon to launch the macro programming environment.
- 3. Paste and Run: Double-click your active sheet in the Project Explorer, paste your automated row-moving script, and save the file.

Frequently Asked Questions
Why isn't my Worksheet_Change macro triggering when I type 'DONE'?
This usually happens if `Application.EnableEvents` was left set to `False` due to a previous macro error, or if macros are disabled in your Trust Center settings. You can manually type `Application.EnableEvents = True` in the Immediate Window (Ctrl+G) and press Enter to restore functionality.
Can I undo the row move if I accidentally type 'DONE'?
No. Actions performed by VBA macros clear the standard Undo stack in the spreadsheet. If you accidentally trigger the macro, you will need to manually cut the row from the completed sheet and paste it back into your active list.
How do I prevent the macro from crashing if I delete multiple rows at once?
If a user selects and deletes multiple cells, the `Target` range contains multiple cells, which can cause type mismatch errors in your `If` statement. Add `If Target.Cells.Count > 1 Then Exit Sub` at the very beginning of your Worksheet_Change event to ignore multi-cell modifications.
Will this macro copy my drop-down data validations to the completed sheet?
Yes, using the `Target.EntireRow.Copy` method duplicates all aspects of the row, including values, cell background colors, fonts, and data validation rules.




