VBA & Macro Problems

Move Completed Excel Rows to Another Worksheet with VBA

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

Move a task row from the Current worksheet to the Completed worksheet when a completion date is entered in column D.

Move Completed Excel Rows to Another Worksheet with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
The Current and Completed sheets have matching headers, and completed tasks should be moved automatically.
Observed behavior
Entering a valid completion date in column D should move the entire task row to the bottom of Completed and remove it from Current.
Before you start

Confirm that the worksheets are named Current and Completed, both use matching headers in row 1, and neither sheet is protected. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and allow macros only if you trust the file.

Solution 1Recommended

Use a Worksheet_Change macro to move completed rows

Use this approach to move rows automatically when valid dates are entered or pasted into column D.

The event runs when cells are edited or pasted, not when a formula recalculates. If several dates are pasted at once, rows are processed from bottom to top to prevent skipped rows; their order on Completed will therefore be reversed.

The macro moves entire worksheet rows. It may need adjustment if the data uses Excel tables, merged cells, or other structures that restrict row operations.

1
Open the Current sheet module

Right-click the Current sheet tab, select View Code, and paste the following code into that worksheet's code window, not a standard module: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim changed As Range, c As Range, lastCell As Range Dim completedSheet As Worksheet Dim rowNumbers() As Long Dim count As Long, i As Long, j As Long, temp As Long Dim destinationRow As Long, invalidCount As Long Dim value As Variant, errorMessage As String Dim previousEvents As Boolean previousEvents = Application.EnableEvents On Error GoTo Failed Set changed = Intersect(Target, Me.Columns("D")) If changed Is Nothing Then Exit Sub ReDim rowNumbers(1 To changed.Cells.CountLarge) For Each c In changed.Cells If c.Row > 1 Then count = count + 1 rowNumbers(count) = c.Row End If Next c ' Sort from bottom to top so deleting rows does not skip entries. For i = 1 To count - 1 For j = i + 1 To count If rowNumbers(j) > rowNumbers(i) Then temp = rowNumbers(i) rowNumbers(i) = rowNumbers(j) rowNumbers(j) = temp End If Next j Next i Set completedSheet = ThisWorkbook.Worksheets("Completed") Application.EnableEvents = False For i = 1 To count value = Me.Cells(rowNumbers(i), "D").Value If IsError(value) Then invalidCount = invalidCount + 1 ElseIf Len(Trim$(CStr(value))) > 0 Then If IsDate(value) Then Set lastCell = completedSheet.Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False) destinationRow = 2 If Not lastCell Is Nothing Then destinationRow = Application.Max(2, lastCell.Row + 1) End If Me.Rows(rowNumbers(i)).Cut Destination:=completedSheet.Rows(destinationRow) Me.Rows(rowNumbers(i)).Delete Else invalidCount = invalidCount + 1 End If End If Next i CleanUp: Application.EnableEvents = previousEvents If Len(errorMessage) > 0 Then MsgBox errorMessage, vbExclamation If invalidCount > 0 Then MsgBox invalidCount & " completion date(s) were invalid and were not moved.", vbExclamation Exit Sub Failed: errorMessage = "Error " & Err.Number & ": " & Err.Description Resume CleanUp End Sub

2
Save and enable the macro

Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Reopen the workbook if necessary and enable macros through the security prompt only if you trust the file.

3
Test a completion date

On Current, enter a valid date in column D of a task row. Check that the row appears below the existing data on Completed and is removed from Current.

Use a Worksheet_Change macro to move completed rows
Invalid dates stay on Current: Blank entries do not move a row. Nonblank values that Excel does not recognize as dates remain on Current and produce a warning.
Free Microsoft Office alternative

Work with task spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easier to open and work with existing files. Excel VBA event macros may not work the same way in WPS, so test this automation in Excel.

Review and edit task lists in WPS SpreadsheetsOpen and work with Microsoft Excel XLSX and XLS filesFamiliar spreadsheet interface for easier migrationFree, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Where should I paste the VBA code?

Right-click the Current sheet tab, select View Code, and paste the event procedure into that worksheet's code window. It will not run as a worksheet event if placed in a standard module.

Why doesn't a row move when its date is calculated by a formula?

Worksheet_Change responds to edits and pastes, not formula recalculation. Enter the date directly in column D or use a different event-based approach designed for recalculation.

Can I paste completion dates into several rows at once?

Yes. The macro processes changed column D cells from bottom to top so deletions do not skip rows. As a result, those rows appear in reverse order on Completed.

Why is my row still on the Current sheet?

Check that column D contains a nonblank date Excel recognizes, macros are enabled for the trusted workbook, and the destination sheet is named Completed. Protected sheets or restricted row operations can also prevent the move.

Can a formula move the task row instead of VBA?

A formula can display data from another sheet, but it cannot delete the original row. Use a macro or move the row manually if it must be removed from Current.