Move a Row to Another Excel Sheet When Its Status Changes
Question details
Move a record from one Excel worksheet to another when its status is changed to Closed.

- 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.
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.
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.
In Excel, press Alt+F11. In the VBA editor's Project Explorer, double-click the source worksheet under your workbook's Microsoft Excel Objects.
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
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.

Filter records by status without moving rows
Use a filter if you only need separate views of open and closed records.
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.

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.




