runtime error 9 subscript out of range

[Nastack628]

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

I followed a post from this site saying to paste this vba code int
worksheet one with the only change being the "word" used for th
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 erro
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

 

[Gord Dibben]

Do you have a Sheet2


Gord