VBA & Macro Problems

How to Automatically Move an Excel Row When Status Changes to Complete

Maira Mehtab Sep 20, 2026 868 views

Question details

The user wants to configure a case-log workbook so that an entire row automatically moves from the active worksheet to a designated destination worksheet whenever a specific column's status is updated to Complete.

Product: Excel
Device & OS: not provided
Scenario: Managing a dynamic case log or task tracker across multiple worksheets based on the current status of the task.
Observed behavior: The user requires an automated process (such as a macro) to trigger the row transfer upon a status change, eliminating the need to manually copy, paste, and delete rows.

Before you start

Ensure you have saved your workbook as an Excel Macro-Enabled Workbook (.xlsm) and identify the exact names of your source and destination worksheets, as well as the column letter containing the status drop-down.

Solution 1Recommended

Use a VBA Worksheet_Change Event to Move Completed Rows

Applying a VBA Worksheet_Change event is the most effective way to automatically cut and paste rows to another sheet the moment a specific cell value is updated.

This method involves running a background event handler on your source worksheet. Whenever the designated status column is changed to 'Complete', the macro is triggered automatically. It copies the target row, finds the next empty row in the destination sheet, pastes the data, and deletes the original row.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Select the Target Source Worksheet

In the Project Explorer pane on the left side, double-click the name of the source worksheet (e.g., 'Sheet1' or 'Active Cases') where you will be manually updating the task status.

Insert the Macro Code

Change the first drop-down menu above the blank code window from '(General)' to 'Worksheet', and the second drop-down to 'Change'. Input a VBA script that checks if the Target.Column matches your status column index and if Target.Value equals 'Complete'. The script should then cut the Target.EntireRow and paste it into the destination sheet.

Save as a Macro-Enabled Workbook

Close the VBA editor and go to File > Save As. Choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format drop-down list to ensure your automation continues to run in the future.

Enable Macros in Trust Center: You must enable macros in your Excel Trust Center settings, or click 'Enable Content' when a security warning appears, for the automation to work when you reopen the file.

Automate Tasks with WPS Office

Use WPS Spreadsheet to Automate Row Movements

WPS Office fully supports VBA macros via its Developer tools, allowing you to seamlessly automate workflows like moving completed rows between worksheets without any hassle.

1. Enable Developer Tools: Open WPS Spreadsheet, go to the 'Developer' tab on the top ribbon, and ensure your VBA environment is active.
2. Access the VBA Editor: Click the 'Visual Basic' button or press Alt + F11 to launch the code editor.
3. Insert Code and Save: Double-click your source sheet in the project pane, paste your Worksheet_Change event code for moving rows, and save the document as an .xlsm file.

Highly compatible with Microsoft Excel macro (.xlsm) formats.Built-in Developer tools for writing, editing, and running VBA code.Lightweight architecture that runs smoothly even on older devices.Free to use for the majority of everyday office tasks and basic automation.

microsoft office alternative - wps office

Frequently Asked Questions

Can I automatically move rows using formulas instead of VBA?

No, standard Excel formulas cannot physically move or delete rows. Formulas can only replicate or pull data into another sheet (using functions like FILTER or VLOOKUP). To physically move a row and delete it from the original sheet automatically, VBA macros or Office Scripts are required.

Why did my macro stop working after I saved and closed the file?

This typically happens if the file was saved as a standard Excel Workbook (.xlsx) instead of a Macro-Enabled Workbook (.xlsm). The .xlsx format strips all VBA code for security reasons. Always ensure you 'Save As' and select the .xlsm format.

How do I undo a row move triggered by a macro?

Actions performed by VBA macros clear the Excel Undo history. You cannot simply press Ctrl + Z to reverse the row move. If a row is moved by mistake, you will need to manually cut and paste the row back to the original sheet.

Excel & Data Problems