VBA & Macro Problems

Move Excel Rows to a Completed Sheet When Column J Says YES

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Automatically move a row to a Completed sheet when YES is entered in column J.

Move Excel Rows to a Completed Sheet When Column J Says YES
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the source sheet's code

In desktop Excel, right-click the source worksheet tab, such as Sheet1, and select View Code.

2
Add the event macro

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

3
Save as a macro-enabled workbook

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Enable macros for the workbook when prompted.

4
Test the move

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.

Use a Worksheet_Change macro to move completed rows
Existing YES values: The macro responds to new edits. Existing YES values are not moved until their cells are edited again.
Free Microsoft Office alternative

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.

Organize completed records in a separate worksheetOpen and edit common Excel formats, including XLSX and XLSNavigate spreadsheets with a familiar interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

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.