Auto move row to different sheet after entering completion date

hmcaba 

The copying of data from one place to another can be done with a formula, but the movement of data (rows) from one place to another is not. You will need to use (VBA) procedural code. That requirement will limit you to a version of Excel that supports VBA scripts – a version of Excel for either Windows or Mac – and the workbook must be stored as a macro-enabled workbook (typically with a .xlsm extension). And it also means that the "someone" who enters a date will also have the ability to change the workbook's code – and potentially do something malicious. So you need to be more concerned with security than before. And you or someone at your workplace needs to become familiar with VBA and the Excel object model.

Each worksheet's code can optionally include a Worksheet_Change event handler; such an event handler will be invoked whenever a user (or software, but with exceptions) makes a change to a value on that worksheet. So the VBA code relevant to your requirement must go into a Worksheet_Change event handler for your Current worksheet. The code will be written to ignore changes other than in column D.

Here is code you might use:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strMessage  As String   'a message to the user
    '  --   In this worksheet:
    Dim rngIntersection As Range    'changed cells of interest
    Dim rngCell     As Range
    Dim in4RowNum   As Long
    Dim vntStoredValue  As Variant
    '  --   For working with the Completed worksheet:
    Dim objCompletedSht As Worksheet
    Dim in4LastUsedRow  As Long
    
    '----
    On Error GoTo WorkshtChg_ErrHndlr
    '
    Application.EnableEvents = False    '...because this procedure may change _
            the content of this and/or other worksheets, and we don't want _
            those changes to fire additional events
    
CheckForChangeOfCompleted:
    '----   Check for a change in value(s) in the CompletedDt column.
    Set rngIntersection = Intersect(Target, Me.Range("D:D"))
    If rngIntersection Is Nothing Then
        '...the change(s) did not involve the column of interest.
        GoTo CheckForNextChangeTypeOfInterest
    End If
    '   Normally we would loop through the intersection cells using:
    'For Each rngCell In rngIntersection
    '   ...but because the code in this loop may delete one or more rows,
    '   loop from the bottom up instead:
    For in4RowNum = 200 To 2 Step -1    '[your range of rows may be different, _
            depending on how many header rows you have and how much data you _
            expect]
        Set rngCell = Me.Range("D" & in4RowNum)
        '  --   Capture info.
        vntStoredValue = rngCell.Value
        If vntStoredValue = "" Then
            '   Either it was unchanged, or an invalid value was cleared.
            '   In either case, no action is needed for this row.
        ElseIf IsDate(vntStoredValue) Then
            If vntStoredValue < #1/1/2020# _
            Or vntStoredValue > #12/31/2029# Then '[specify your own valid date range]
                '   Warn the user.
                strMessage = "The Completion Date value in row " & in4RowNum _
                    & " is not a valid date: " & vntStoredValue
                Call MsgBox(strMessage, vbExclamation)
            Else
                '  --   Locate the last-used row in the Completed worksheet,
                '       based on content in column D.
                Set objCompletedSht = Sheets("Completed")
                With objCompletedSht
                    in4LastUsedRow = .Range("D" & .Rows.Count).End(xlUp).Row
                End With
                '  --   Move this row in the Current worksheet to the bottom of
                '       the Completed worksheet.
                Me.Range("A" & in4RowNum).EntireRow.Cut _
                    objCompletedSht.Range("A" & (in4LastUsedRow + 1)).EntireRow
                Me.Range("A" & in4RowNum).EntireRow.Delete xlUp
            End If
        Else
            '   Warn the user.
            strMessage = "The Completion Date value in row " & in4RowNum _
                & " is not a valid date: " & vntStoredValue _
                & " (" & TypeName(vntStoredValue) & ")"
            Call MsgBox(strMessage, vbExclamation)
        End If
    Next
    
CheckForNextChangeTypeOfInterest:
    'There is none, so:
    Application.EnableEvents = True
    Exit Sub

WorkshtChg_ErrHndlr:
    Dim in4ErrorCode    As Long
    Dim strErrorDescr   As String
    '  --   Capture info.
    in4ErrorCode = Err.Number
    strErrorDescr = Err.Description
    '  --   Notify the user.
    strMessage = "Error " & in4ErrorCode & " occurred:" & vbCrLf & strErrorDescr
    Call MsgBox(strMessage, vbCritical)
    '  --
    Application.EnableEvents = True
    Exit Sub
End Sub

(And I recommend always specifying an Option Explicit statement at the top of the code module.  That makes VBA catch misspelled variable names.)