Auto move row to different sheet after entering completion date

Copper Contributor

Hello! I am very new to excel and can’t really do much beyond the basics and having trouble figuring this out. 
I have a table full of tasks for my team in a sheet named ‘current’ and column d is for completion dates. I need it so once someone enters a date in that column the whole row will be moved to the sheet labeled ‘completed’ which has the same headers from the original sheet. 
Any and all help and advice would be very much appreciated! Thank you!

9 Replies

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

 

If you have multiple sheets with data having same header names and are in the same cell range then you can group the sheets by holding the SHIFT key and click the last sheet . Make sure all these sheets should be in sequently arranged.

Now when you enter data in your first sheet, the values/ any editings all get updated in each sheet in one go.

Hope this helps.
Asina mester ta kla pa usa

@SnowMan55 This is a great macro, which works well for me, with one exception - I do not want to delete the row in the Current sheet, only clear out the data that was in it. So, essentially just move the data in that row to the Completed sheet. How would I adjust that macro? Thanks!

rem Me.Range("A" & in4RowNum).EntireRow.Delete xlUp
Me.Range("A" & in4RowNum).EntireRow.clearcontents
https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.ClearContents
Is there a way to have the row that is moved to the "Completed" sheet go to the top of the of list rather than to the bottom of the list?

If you keep all datas in Sheet1,you dynamic filter both Completed and not Complete with sql like below:

set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;';Data Source=" & ThisWorkbook.FullName
sql="select * from [Sheet1$]"
fields="*"
criteria=" where instr(""Completed"",Status)>0"
sql="select " & fields & " from [Sheet1$]" & criteria & " order by rowid desc"

'you should have a column rowid in Sheet1 for order the records desc
Set Rst=CreateObject("ADODB.Recordset")

Rst.CursorLocation = 3
Rst.open sql,conn,1,3

ActiveCell.CopyFromRecordset Rst

Thanks @peiyezhu! I have a separate sheet titled "Completed," so moving them from Sheet 1 to Completed. I would like the most recent completed rows to go to the top of the "Completed" sheet so it is oldest on the bottom and newest at the top.

 

LenaGonzo_0-1708468201280.png

 

 

 

Do we add the SQL underneath our code in our Module?  

 

I have attached a snippet of the codes that I currently have.

 

 

would like the most recent completed rows to go to the top of the "Completed" sheet
This code about ADO is mean to extract all complete records from master sheet sheet1 rather than most recent completed rows because you want the new complete rows should at top of the list.