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!

Do you have a Sheet2


Gord



On Thu, 2 Feb 2012 15:29:08 +0000, Nastack628
wrote:


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!