I've never seen anyone ask this before - I've included code for a large Excel table I have. For the first statement of the vba, I've got it set so when a date is entered in a particular cell, the entire row automatically rolls over to a different tab in the workbook. The code just picks up on if the cell is empty or not.
All works perfect.
Now, what they want is to have the row move over to the new tab one day after the date that is entered in the cell. So for example, if the date entered is 03/01/16, they don't want the row to move over until 03/02/16.
I did not know if possible with vba? Thanks!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Dim WkRg As Range
If (Target.Column = 27) Then
If (Target.Value = "<>""") Then Exit Sub
Set WkRg = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AH"))
Application.EnableEvents = False
With Sheets("COMPLETED")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
WkRg.Copy Destination:=.Cells(LastRow + 1, 1)
WkRg.EntireRow.Delete
End With
Application.EnableEvents = True
Exit Sub
Else
If (Target.Column <> 28) Then Exit Sub
If (Target.Value = "<>""") Then Exit Sub
Set WkRg = Range(Cells(Target.Row, "A"), Cells(Target.Row, "AH"))
Application.EnableEvents = False
With Sheets("INACTIVE")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
WkRg.Copy Destination:=.Cells(LastRow + 1, 1)
WkRg.EntireRow.Delete
End With
Application.EnableEvents = True
End If
End Sub
Now, what they want is to have the row move over to the new tab one day after the date that is entered in the cell. So for example, if the date entered is 03/01/16, they don't want the row to move over until 03/02/16.
I did not know if possible with vba? Thanks!