date | call | info | lead | ||||||||
Hello,
I'm a real newb and any help would greatly appreciated.
I have 3 sheets that contains contact info. named "hot" "cold" and "lost".
I would like it so that when "cold" is entered in the "lead" column, the row moves to the "cold" sheet and if "lost" is entered moves to "lost" sheet, if nothing is entered the row stays on sheet. The same thing for the other 2 sheets.
There are 12 columns, lead is col. "L" and row "1" is header.
I tried this code on hot sheet, but when it runs I get: run time error "9", subscript out of range.
Any help would greatly appreciated, thanks
Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim lr As Long With Target If .Rows.Count = 1 And .Column = Columns("L").Column Then If LCase(.Value) = "cold" Or LCase(.Value) = "lost" Then .EntireRow.Copy lr = Worksheets(.Value).Cells(Rows.Count, "L").End(xlUp).Row Worksheets(.Value).Paste Worksheets(.Value).Cells(lr + 1, "A") Application.EnableEvents = False .EntireRow.Delete Application.EnableEvents = True End If End If End With End Sub
Last edited: