Conditionally Copy Row to New Worksheet

[funky4]

Right Now I have four work sheets set up, "Main" "Contract" "Sales" and "Rentals"

In all 4 worksheets Row 1 is the same 5 first columns
"Job" "Status" "City" "State" "Contractor"

I'm having trouble coding something that will always run (I don't want to have to run the macro, just want it to be automatic when the "Status" column is changed.)

What I'm trying to get to happen is when the "Status" column is changed in "Main" then it will copy and paste that row to one of the other 3 worksheets based on the string entered in Main.

If the string in "Status" was C, I want it to copy to Contract
S to Sales
R to Rentals

Essentially I have a lot of jobs to look at and I want them all in main,
but as a type of sorting, I want to look at all the Contract, Sales, and Rentals in separate sheets. I don't want to do this manually because people are always updating this worksheet and I just want the worksheet to do it on a continual basis for them. This way we can track where are our work is going at the time.

I'm just having trouble coding this, any help would be greatly appreciated!

FuN7(y

 

[funky4]

Any thoughts?

 

[funky4]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 Then
ThisRow = Target.Row
If Target.Value = "HD" Then
Rows(ThisRow).Select
Selection.Copy
Sheets("Contract").Select
L = 2
Do While L > 1
Cells(L, 1).Select
If Cells(L, 1) = "" Then
Rows(L).Select
ActiveSheet.Paste
L = 0
Else: L = L + 1
End If
Loop
ElseIf Target.Value = "HR" Then
ThisRow.Select
Selection.Copy
Sheets("Rental").Select
L = 2
Do While L > 1
Cells(L, 1).Select
If Cells(L, 1) = "" Then
Rows(L).Select
ActiveSheet.Paste
L = 0
Else: L = L + 1
End If
Loop
ElseIf Target.Value = "HS" Then
ThisRow.Select
Selection.Copy
Sheets("Sales").Select
L = 2
Do While L > 1
Cells(L, 1).Select
If Cells(L, 1) = "" Then
Rows(L).Select
ActiveSheet.Paste
L = 0
Else: L = L + 1
End If
Loop
End If
End If
End Sub

 

[funky4]

Thats what I've come up with so far, but I'm getting an error when it comes to my Cells(L,1).Select area