Hello Everyone,
Attempting to create a script to move a row from one worksheet to another based on two separate cells. The first cell is a drop down list which will move only if showing as completed. The second cell is a date cell and needs to move when it is not blank and the first cell is listed as completed. Column C is the drop down list and Column F is the completed date section. Need the script to move the row when both conditions are true. Can get the row to move when just the drop down list is the condition (below) but any combination to include the date condition has failed. Might be overthinking it but would greatly appreciate any help. The current script works as follows:
Attempting to create a script to move a row from one worksheet to another based on two separate cells. The first cell is a drop down list which will move only if showing as completed. The second cell is a date cell and needs to move when it is not blank and the first cell is listed as completed. Column C is the drop down list and Column F is the completed date section. Need the script to move the row when both conditions are true. Can get the row to move when just the drop down list is the condition (below) but any combination to include the date condition has failed. Might be overthinking it but would greatly appreciate any help. The current script works as follows:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
'If Cell that is edited is in column C and F and the value is completed then
If Target.Column = 3 And Target.Value = "Completed" Then
'Define last row on completed worksheet to know where to place the row of data
LrowCompleted = Sheets("Archive").Cells(Rows.Count, "A").End(xlUp).Row
'Copy and paste data
Range("A" & Target.Row & ":BA" & Target.Row).Copy Sheets("Archive").Range("A" & LrowCompleted + 1)
'Delete Row from Project List
Range("A" & Target.Row & ":BA" & Target.Row).Delete xlShiftUp
End If
Application.EnableEvents = True
End Sub