I am trying to set up my excel workbook to copy an entire row of information into different worksheets when certain criteria is met.

I followed a post from this site saying to paste this vba code into worksheet one with the only change being the "word" used for the criteria, in this case "InProcess".

Option Compare Text 'makes text non-case sensitive
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100"
Dim srcerng, targrng As Range
n = Target.Row
Set srcerng = Range(Cells(n, "B"), Cells(n, "D"))
Set targrng = Sheets("Sheet2").Cells(Rows.Count, _
1).End(xlUp).Offset(1, 0)
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False
On Error Resume Next
If Target.Value = "yes" Then
srcerng.Copy Destination:=targrng
End If
End If
endit:
Application.EnableEvents = True
End Sub

When I start adding data to the different cells i get the runtime error 9 and the debug indicates this is the line causing the problem:

Set targrng = Sheets("Sheet2").Cells(Rows.Count, _
1).End(xlUp).Offset(1, 0)

How do I correct this issue?

Thanks!