I have the following code:-

Sub CopyRows()
' copy all rows of data in worksheet 'temp' to the appropriate worksheets
Dim iLooper As Long, NextRow As Long, ws As Worksheet, strSheet As String
With Worksheets("temp")
For iLooper = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
If UCase(Trim(.Cells(iLooper, "B").Value)) Like "ENROLLMENT*" Then
strSheet = "ENROLLMENT"
ElseIf UCase(Trim(.Cells(iLooper, "B").Value)) Like "WEEK*" Then
strSheet = .Cells(iLooper, "a").Text
Else
strSheet = ""
End If
If strSheet <> "" Then
Set ws = Worksheets(strSheet)
NextRow = ws.Columns("B").Find("*", , xlValues, , 1, 2).Row + 1
ws.Rows(NextRow).Insert
ws.Cells(NextRow, "A").Resize(, 8).Value = _
.Cells(iLooper, "A").Resize(, 8).Value
End If
Next
End With
' delete all rows of data in worksheet 'temp'
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
End Sub

When run I get a run-time error '9': Subscript out of range together with the bold, italic line above highlighted



The routine processes all the lines as they should be - and so the error occurs at the end of processing and, of course, the lines of data in 'temp' are not erased

Would welcome any comments on how to correct the code..........

Bob M

Duplicate of: https://www.msofficeforums.com/excel...-question.html

Thread closed.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]