VBA & Macro Problems

How to Automatically Move DONE Rows and Sort Work Orders with VBA in Spreadsheets

Olivia MillerOlivia Miller Nov 11, 2026 870 views

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.

How to Automatically Move DONE Rows and Sort Work Orders with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the Worksheet Code Module

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.

2
Set Up the Event and Disable Recursion

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.

3
Define the Trigger Condition

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'.

4
Copy the Row and Preserve Formatting

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.

5
Delete Original Row and Sort

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.

6
Re-enable Events and Save as XLSM

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.

Implement a Worksheet_Change Event Macro
Handling Protected Sheets: If your worksheet is password protected, the macro will crash when attempting to move rows. You must add `ActiveSheet.Unprotect "YourPassword"` at the beginning of the script and `ActiveSheet.Protect "YourPassword"` at the end.
Advanced Spreadsheet Automation

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. 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. 2. Open the VB Editor: Click the 'VB Editor' button on the ribbon to launch the macro programming environment.
  3. 3. Paste and Run: Double-click your active sheet in the Project Explorer, paste your automated row-moving script, and save the file.
Fully compatible with Microsoft Excel VBA scripts and .xlsm formats.Lightweight architecture ensures fast execution of row movements and sorting algorithms.Built-in Developer tools for easy macro editing and debugging.Familiar VBA editor interface, requiring zero learning curve to migrate.
microsoft office alternative - wps office

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.