VBA & Macro Problems

Move a Row to Another Excel Sheet When Its Status Changes

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Move a record from one Excel worksheet to another when its status is changed to Closed.

Move a Row to Another Excel Sheet When Its Status Changes
Product
Microsoft Excel 2013
Device & OS
not provided
Scenario
A worksheet contains records with statuses such as Open and Closed, and closed records should be transferred to another worksheet.
Observed behavior
A formula alone cannot physically move a row between worksheets.
Before you start

Identify the source and destination sheet names and the status column. The example assumes status values are in column B, both sheets have headers in row 1, and destination column A is populated for every existing record.

Solution 1Recommended

Move closed rows with a Worksheet_Change macro

Use this method to physically move a row when a user edits its status to Closed.

The event runs when a single cell in the specified status range is edited to Closed. It does not verify that the previous value was Open, and it does not run when a formula changes its result.

The example copies the entire row to the next destination row, then deletes it from the source. Disabling events during the operation prevents the deletion from triggering the event again.

1
Open the source sheet module

In Excel, press Alt+F11. In the VBA editor's Project Explorer, double-click the source worksheet under your workbook's Microsoft Excel Objects.

2
Add the event code

Paste the following code into the source worksheet module. Change "Closed Records" to your destination sheet name and B2:B10000 to your status range if needed: Private Sub Worksheet_Change(ByVal Target As Range) Dim destination As Worksheet Dim nextRow As Long Dim errorMessage As String If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Range("B2:B10000")) Is Nothing Then Exit Sub If IsError(Target.Value2) Then Exit Sub If LCase$(Trim$(CStr(Target.Value2))) <> "closed" Then Exit Sub On Error GoTo Failed Application.EnableEvents = False Set destination = ThisWorkbook.Worksheets("Closed Records") nextRow = destination.Cells(destination.Rows.Count, "A").End(xlUp).Row + 1 Me.Rows(Target.Row).Copy Destination:=destination.Cells(nextRow, "A") Me.Rows(Target.Row).Delete CleanUp: Application.EnableEvents = True If Len(errorMessage) > 0 Then MsgBox errorMessage, vbExclamation Exit Sub Failed: errorMessage = "The row could not be moved: " & Err.Description Resume CleanUp End Sub

3
Save and test the workbook

Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Enable macros if prompted, then change a test record's status to Closed and check the destination sheet.

Move closed rows with a Worksheet_Change macro
Check after an error: If copying succeeds but deleting fails, the row may remain on both sheets. Check both worksheets before retrying.
Free Microsoft Office alternative

Manage status-based records in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and filter records by status as you migrate everyday spreadsheet work; it does not repair or run Excel's Worksheet_Change event inside Microsoft Excel.

Filter spreadsheet records by Open or Closed statusOpen and edit compatible Microsoft Excel XLSX and XLS filesUse a familiar interface for everyday spreadsheet tasksWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can an Excel formula move a row to another sheet?

No. A formula can display values from another sheet, but it cannot delete the source row or physically move it. Use a VBA event for that task, or use Data > Filter to show records by status.

Where should I put the Worksheet_Change code?

Press Alt+F11 and open the source worksheet under Microsoft Excel Objects in Project Explorer. Paste the event procedure into that worksheet's code window, not a standard module.

Why doesn't the macro run when a formula changes the status?

Worksheet_Change responds to cell edits, not changes in calculated results. A formula-driven status requires a different approach, such as a calculation-based procedure that tracks previous values.

Will the macro move several rows if I paste multiple statuses at once?

No. The example exits when more than one cell is changed. Edit statuses one at a time or adapt the code to process multiple rows.

Why must I save the workbook as an XLSM file?

The XLSM format preserves VBA code. Choose File > Save As > Excel Macro-Enabled Workbook (*.xlsm), and enable macros when prompted if you trust the workbook.