Move Completed Excel Rows to Another Worksheet with VBA
Question details
Move a task row from the Current worksheet to the Completed worksheet when a completion date is entered in column D.

- 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.
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.
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.
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
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.
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.

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.

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.




