Move Excel Rows to a Completed Sheet When Column J Says YES
Question details
Automatically move a row to a Completed sheet when YES is entered in column J.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user tracks items on a source worksheet and wants completed items moved to a separate worksheet.
- Observed behavior
- The entire row should be copied to the next available row on Completed and deleted from the source sheet.
Make sure the workbook has a destination sheet named Completed and that its column layout matches the source sheet. Use desktop Excel with macros enabled; Excel for the web cannot run VBA macros.
Use a Worksheet_Change macro to move completed rows
Use this event macro when entering YES in column J should move the corresponding row automatically.
The event runs when cells are edited, not when a formula recalculates to YES. It handles a single cell or a contiguous pasted range in column J, but ignores changes to multiple noncontiguous areas.
Test the macro on a sample workbook first. Moving rows can affect formulas, formatting, and references.
In desktop Excel, right-click the source worksheet tab, such as Sheet1, and select View Code.
Paste the following code into the source worksheet's code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim watched As Range Dim dest As Worksheet Dim lastCell As Range Dim destRow As Long Dim r As Long Set watched = Intersect(Target, Me.Columns("J")) If watched Is Nothing Then Exit Sub If watched.Areas.Count > 1 Then Exit Sub On Error GoTo CleanUp Set dest = ThisWorkbook.Worksheets("Completed") Application.EnableEvents = False For r = watched.Row + watched.Rows.Count - 1 To watched.Row Step -1 If Not IsError(Me.Cells(r, "J").Value2) Then If UCase$(Trim$(CStr(Me.Cells(r, "J").Value2))) = "YES" Then Set lastCell = dest.Cells.Find(What:="*", After:=dest.Cells(1, 1), LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If lastCell Is Nothing Then destRow = 1 Else destRow = lastCell.Row + 1 End If Me.Rows(r).Copy Destination:=dest.Rows(destRow) Me.Rows(r).Delete End If End If Next r CleanUp: Application.EnableEvents = True If Err.Number <> 0 Then MsgBox "Could not move the row: " & Err.Description, vbExclamation End Sub
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Enable macros for the workbook when prompted.
On the source sheet, type YES in column J of a sample data row. Check that the row appears after the last used row on Completed and is removed from the source sheet.

Manage Spreadsheet Records with WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to bring existing workbooks over. VBA availability varies by WPS edition, so check macro support before relying on this Excel event macro.

Frequently Asked Questions
Can an Excel formula move and delete the original row?
No. A formula can display data elsewhere, but it cannot delete the source row. Use the Worksheet_Change macro in the source sheet's View Code window for an automatic move.
Why does the macro need to be saved in an XLSM file?
The XLSM format preserves VBA code. Choose File > Save As > Excel Macro-Enabled Workbook (*.xlsm); saving as XLSX removes the macro.
Will the macro move rows if a formula returns YES in column J?
No. Worksheet_Change responds to cell edits, not formula recalculation. The shown macro is intended for values entered or pasted into column J.
What happens if I paste YES into several rows at once?
A contiguous paste into column J is processed from the bottom row upward. The macro ignores changes involving multiple noncontiguous areas.
Why is my row not moving after I type YES?
Check that macros are enabled, the code is in the source worksheet's View Code window, and the destination tab is named Completed. The macro must run in desktop Excel, not Excel for the web.




