Excel - runtime error 9 subscript out of range

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!




--
Nastack628

Do you have a Sheet2


Gord

A worksheet_change event MUST be in the SHEET

did not finish
MUST be in the SHEET module. It will fire on any and all changes made
so you may want to restrict. Are you inputing data into a row and then
want the data to go where_______
Do you have a sheet named EXACTLY Sheet2??  without leading/trailing
spaces. I would do this in a simpler way.....