Hi, can someone help me to modify the coding below that currently works if column F target equals "Contracts signed & received" which automatically transfers the row of data from the tab "ongoing" to tab "New Clients". Now I need to add another condition whereby if the target in column F is equal to "Business Lost" this row of data is automatically moved to tab "Lost Business". I have attached an example workbook.
Thanks
Option Explicit
Dim Flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
If Flag = True Then Exit Sub
If Not Intersect(Target, Range("F3:F" & LR)) Is Nothing Then
If Target.Value = "Contract signed & received" Or Target.Value = "On Hold" Then
LR = Sheets("New Clients").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy
Sheets("New Clients").Range("A" & LR).PasteSpecial
Flag = True
Target.EntireRow.Delete
End If
End If
Application.CutCopyMode = False
Flag = False
End Sub
Bookmarks