VBA & Macro Problems

Automate Moving Closed Excel Rows to Another Sheet with VBA

Camila Milosovich Sep 28, 2026 869 views

Question details

The user needs to automate the process of moving rows marked as 'CLOSED' from an active worksheet to a designated archive worksheet, adding a completion date, and deleting the original row.

Automate Moving Closed Excel Rows to Another Sheet with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing a task tracking spreadsheet where completed items currently require manual copying, deleting, moving, and timestamping.
Observed behavior: The user is currently copying columns A-E, deleting the row manually, pasting it into the closed sheet, and manually typing the closing date in column F.

Before you start

Before modifying your spreadsheet with VBA, save a backup copy of your workbook and ensure that the Developer tab is enabled in your Excel ribbon.

Solution 1Recommended

Use a Worksheet_Change Event to Move Rows Automatically

This method runs automatically in the background whenever you type a specific value (like 'CLOSED') into a designated column.

A Worksheet_Change event is tied to a specific sheet. It continuously listens for changes and executes the code instantly when your target conditions are met.

When deleting rows with VBA, the code must loop through the rows from bottom to top (backwards) to prevent the row indices from shifting and skipping rows.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor.

Access the Source Sheet Module

In the Project Explorer pane on the left, double-click the name of your active sheet (e.g., 'Sheet1 (OPEN)') where you update the status.

Insert the Event Code

Select 'Worksheet' from the left dropdown above the code window and 'Change' from the right dropdown. Insert a macro that checks if the Target.Column is 6 (Column F) and Target.Value is 'CLOSED'.

Define the Transfer and Delete Logic

Within your event code, instruct VBA to copy the row, find the next empty row on the destination sheet, paste the data, add `Date` to column F, and finally use `Target.EntireRow.Delete`.

Save as a Macro-Enabled Workbook

Close the VBA Editor. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your automation script is saved.

Use a Worksheet_Change Event to Move Rows Automatically

Important Code Precaution: Wrap your code with Application.EnableEvents = False at the beginning and True at the end to prevent the macro from infinitely triggering itself when it makes changes to the sheet.

Solution 2

Create a Clickable Button to Archive Closed Rows in Batches

Assign a standard macro to a button if you prefer manually triggering the archive process at the end of the day instead of immediately moving rows upon typing.

Powerful Spreadsheet Automation

Automate Workflows Easily with WPS Spreadsheets

WPS Office provides excellent support for VBA macros, allowing you to seamlessly automate tasks like transferring rows and updating project trackers with high efficiency.

1. Open Your Project Tracker: Launch WPS Office and open your existing task management workbook.
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on the 'VBA Editor' icon.
3. Insert the Automation Script: Paste your Worksheet_Change event or standard loop macro into the appropriate sheet module or standard module.
4. Run and Save: Test the row-moving automation and save your document as a Macro-Enabled Workbook (.xlsm).

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .csv) formats.Includes a robust VBA editor for advanced spreadsheet automation.Lightweight architecture handles complex scripts and large datasets smoothly.Familiar tabbed user interface ensures a zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

How do I loop backwards in VBA when deleting rows?

When iterating through rows to delete them, you must use a For loop with the 'Step -1' instruction, for example: `For i = lastRow To 2 Step -1`. This ensures that deleting a row does not shift the row numbers of the remaining unprocessed rows.

Why isn't my Worksheet_Change event firing?

The event might be disabled if a previous macro encountered an error while `Application.EnableEvents = False` was active. You can re-enable it by opening the VBA Editor, pressing Ctrl+G to open the Immediate Window, typing `Application.EnableEvents = True`, and pressing Enter.

Do both the source and destination worksheets need to be in the same workbook?

The standard Worksheet_Change solution assumes both sheets are in the same active workbook. To move rows to an entirely different closed workbook file, your VBA macro must contain additional code to quietly open that target workbook, paste the data, save it, and then close it.

Excel & Data Problems