I have an excel worksheet that is set to update the next cells with a date if a value was changed in a cell.
This then copies the entire row to another worksheet called Changes if any value in K, M changes
In Cell K is the data and in L is the date it was last amended.
The same in Cells M & N then again in O and P shows when the value equal completed or wholesale.
What I am wanting is to add in backward order so if Cell P date is older than 30 days delete row but if the cell is blank then check the date in cell N and if this cell is blank then check cell L.
If Application.Intersect(Target, Union(Range("k:k"), Range("m:m"))) Is Nothing Then Exit Sub
If Target <> "COMPLETED" And Target <> "WHOLESALE" Then
Target.Offset(0, 1) = Date
Target.Offset(0, 1).NumberFormat = "dd-mm-yy"
End If
Set wsCopy = ActiveSheet
Set wsPaste = ActiveWorkbook.Worksheets("CHANGES")
Set rngCopy = wsCopy.Range("a" & Target.Row & ":q" & Target.Row)
rngCopy.Select
Set rngPaste = wsPaste.Range("a" & wsPaste.Range("a" & Rows.Count).End(xlUp).Row + 1)
rngCopy.Copy
rngPaste.PasteSpecial
Application.CutCopyMode = False
Set rngCopy = Nothing
Set rngPaste = Nothing
Set wsCopy = Nothing
Set wsPaste = Nothing
Public Sub Delete()
'In Changes worksheet
lastrow = Cells(Cells.Rows.Count, "P").End(xlUp).Row
For x = lastrow To 3 Step -1
If IsEmpty(Cells(x, 16).Value <= Date - 30) Then
Cells(x, 16).EntireRow.Delete
ElseIf IsEmpty(Cells(x, 14).Value <= Date - 30) Then
Cells(x, 14).EntireRow.Delete
ElseIf IsEmpty(Cells(x, 12).Value <= Date - 30) Then
Cells(x, 12).EntireRow.Delete
End If
Next
End Sub
This then copies the entire row to another worksheet called Changes if any value in K, M changes
In Cell K is the data and in L is the date it was last amended.
The same in Cells M & N then again in O and P shows when the value equal completed or wholesale.
What I am wanting is to add in backward order so if Cell P date is older than 30 days delete row but if the cell is blank then check the date in cell N and if this cell is blank then check cell L.
If Application.Intersect(Target, Union(Range("k:k"), Range("m:m"))) Is Nothing Then Exit Sub
If Target <> "COMPLETED" And Target <> "WHOLESALE" Then
Target.Offset(0, 1) = Date
Target.Offset(0, 1).NumberFormat = "dd-mm-yy"
End If
Set wsCopy = ActiveSheet
Set wsPaste = ActiveWorkbook.Worksheets("CHANGES")
Set rngCopy = wsCopy.Range("a" & Target.Row & ":q" & Target.Row)
rngCopy.Select
Set rngPaste = wsPaste.Range("a" & wsPaste.Range("a" & Rows.Count).End(xlUp).Row + 1)
rngCopy.Copy
rngPaste.PasteSpecial
Application.CutCopyMode = False
Set rngCopy = Nothing
Set rngPaste = Nothing
Set wsCopy = Nothing
Set wsPaste = Nothing
Public Sub Delete()
'In Changes worksheet
lastrow = Cells(Cells.Rows.Count, "P").End(xlUp).Row
For x = lastrow To 3 Step -1
If IsEmpty(Cells(x, 16).Value <= Date - 30) Then
Cells(x, 16).EntireRow.Delete
ElseIf IsEmpty(Cells(x, 14).Value <= Date - 30) Then
Cells(x, 14).EntireRow.Delete
ElseIf IsEmpty(Cells(x, 12).Value <= Date - 30) Then
Cells(x, 12).EntireRow.Delete
End If
Next
End Sub