Runtime error 9, but only with larger data set

[bbott]

I have a macro that works when I have a data set that is about 1,500 rows or less. When I run the marcro on a data set with 2,000+ rows, I get the error 'Runtime error 9: Subscript out of range.' It errors on this line:

l = Sheets(CStr(y)).Cells(Rows.Count, "O").End(xlUp).Row + 1

Any ideas?

Sub Download()

    Dim x As Long
    Dim y As String
    Dim r As Range
    Dim l As Long

    ActiveSheet.Name = "Download"
    Range("E:E,G:G,H:H,K:K,M:M,O:O,P:P,U:U,Y:Y,Z:Z,AA:AC").Delete
    
    Worksheets.Add.Name = "Managers"
    Worksheets.Add.Name = "Assistant Managers 1"
    Worksheets.Add.Name = "Assistant Managers 2"
    Worksheets.Add.Name = "Team Leaders"
    Worksheets.Add.Name = "Team Members"
    
    Worksheets("Download").Select
    x = 2
    
    Do Until x > Sheets("Download").UsedRange.Rows.Count
        y = Sheets("Download").Cells(x, 15).Value
        Set r = Range("A" & x).EntireRow
        l = Sheets(CStr(y)).Cells(Rows.Count, "O").End(xlUp).Row + 1
        r.EntireRow.Copy Sheets(CStr(y)).Range("A" & l)
        x = x + 1
    Loop
           
End Sub

 

[JLGWhiz]

Subscript out of range indicates that it cannot find the object it has been told to use. In this case Sheets(CStr).
Since y is dependent on Sheets("Download).Cells(x, 15).Value, if that value exceeds the limits of your data or is blank, then you will get the error message.

 

[bbott]

Ah. I had one blank cell in the 2,500 rows of test data. Now it works.

Thanks for the assist!

 

[JLGWhiz]

You are welcome,
Regards, JLG