VBA & Macro Problems

How to Move Completed Rows to Another Excel Worksheet Using VBA

Aamir Naveed Akram Sep 25, 2026 870 views

Question details

The user wants to automatically move an entire row of data from one worksheet to another when a specific column's cell value is changed to indicate completion.

How to Move Completed Rows to Another Worksheet Using VBA

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Automating task workflow by migrating completed jobs to an archive or complete sheet without having to manually cut and paste rows.
Observed behavior: When column O is changed to 'Complete', the target row needs to be instantly copied to the 'Complete' worksheet and deleted from the 'Active Jobs' worksheet.

Before you start

Ensure that macros are enabled in your spreadsheet settings and that your workbook contains the appropriately named destination worksheets before adding the VBA code.

Solution 1Recommended

Using a Worksheet_Change Event to Automate Row Transfer

Add a Worksheet_Change event script to the active worksheet module to automatically transfer and delete rows based on a specific trigger text.

This VBA code listens for changes in column O. When the value 'Complete' is detected, it disables application events to prevent the macro from triggering itself indefinitely. It then copies the data to the destination sheet, deletes the original row, and re-enables events.

Open the VBA Editor

Press 'Alt + F11' while your workbook is open to launch the Visual Basic for Applications (VBA) editor.

Locate the Worksheet Module

In the Project Explorer panel on the left, double-click the specific worksheet (e.g., 'Active Jobs') where the data currently resides.

Insert the VBA Code

Paste your Worksheet_Change event code into the blank code window. Ensure the script designates Column O as the target and correctly specifies the destination worksheet name.

Ensure Proper Event Controls

Check that the code includes 'Application.EnableEvents = False' before copying the row and 'Application.EnableEvents = True' at the end or within an error handler to avoid infinite loops.

Save as a Macro-Enabled Workbook

Navigate to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure the code executes during future sessions.

Using a Worksheet_Change Event to Automate Row Transfer

Worksheet Naming: Make sure that the sheet names referenced in your VBA code exactly match the names on the sheet tabs in your workbook. Extra spaces or typos will cause the macro to fail.

Automate Your Workflows Using VBA in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly run automated row-moving scripts and manage your data workflow effortlessly.

1. Enable VBA in WPS: Open WPS Spreadsheet and navigate to the 'Developer' tab to ensure VBA tools are active.
2. Open the VBA Editor: Click 'Visual Basic' or press 'Alt + F11' to access the macro coding environment.
3. Paste and Run the Code: Insert your Worksheet_Change code into the relevant sheet module, save the workbook as macro-enabled, and test the row transfer trigger.

Fully compatible with Microsoft Excel VBA macros and .xlsm filesLightweight application that executes automated scripts quicklyBuilt-in VBA editor for easy code implementation and debugging

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA code triggering when I type 'Complete'?

Ensure that 'Application.EnableEvents' is currently set to True. If a previous script errored out before turning events back on, triggers will remain disabled until you reset them or restart the application.

Can I trigger the row move using a dropdown list?

Yes, selecting a value from a Data Validation dropdown list in a cell will trigger the Worksheet_Change event just like typing the value manually.

How do I modify the code to check a different column?

Look for a line like `Target.Column = 15` or `Intersect(Target, Range("O:O"))` in your VBA code and change the column number or letter to represent your new target column.

Will this code work if I paste data into multiple rows at once?

A standard Worksheet_Change event might fail or loop incorrectly if multiple cells are changed simultaneously. To handle bulk pasting, the code must include a loop to iterate through each cell in the Target range.

Excel & Data Problems