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! |
runtime error 9 subscript out of range
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! |
All times are GMT +1. The time now is 01:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com