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