Run-Time error "9": Subscript out of Range

[juneau730]

I am working on the following script in Excel, which is extremely similar to another I have working with no issues. The only differences between the working and not working are the column/cell letters, the rest of the functions are the same. When I run this one, I am getting the error in the Title, but I don't have any page breaks set.

Any thoughts/ideas would be greatly appreciated as I am stumped. (I know the code might be a bit janky, but the one that works, does exactly what I need)

Sub VistaCleanUp() Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet, sh5 As Worksheet, sh6 As Worksheet, i As Long, lr As Long
 Set sh1 = Sheets("Audits")
 Set sh2 = Sheets("Inactive Users")
 Set sh3 = Sheets("Never Logged On")
 Set sh4 = Sheets("Inactive Over 90 Days Enabled")
 Set sh5 = Sheets("Inactive Over 365 Days")
 Set sh6 = Sheets("Password Mangement")
 lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    For Each sh In ThisWorkbook.Sheets
    sh2.Range("A2:S7500").ClearContents
    sh3.Range("A2:S7500").ClearContents
    sh4.Range("A2:S7500").ClearContents
    sh5.Range("A2:S7500").ClearContents
    sh6.Range("A2:S7500").ClearContents
    Next
    For i = 2 To lr
        With sh1
            If .Cells(i, "D") <> "" And .Cells(i, "J").Value > 30 And .Cells(i, "J") < 90 Then
                .Rows(i).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
            If .Cells(i, "F") <> "" And .Cells(i, "G").Value <> "" Then
                .Rows(i).Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
            If .Cells(i, "D") <> "" And .Cells(i, "G") <> "" And .Cells(i, "J") >= 90 Then
                .Rows(i).Copy sh4.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
            If .Cells(i, "F") <> "" And .Cells(i, "G") <> "" And .Cells(i, "J") >= 365 Then
                .Rows(i).Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
            If .Cells(i, "G") <> "" And .Cells(i, "K") >= 90 Then
                .Rows(i).Copy sh6.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
       End With
    Next
End Sub

 

[JoeMo]

What line causes the error?

 

[juneau730]

That Sir, is a very good question. When I run the macro, all that pops up is a window with that error message in it. Debugger isn't showing a single line error in the program

 

[JoeMo]

That Sir, is a very good question. When I run the macro, all that pops up is a window with that error message in it. Debugger isn't showing a single line error in the program

That's quite unusual. Hard to help without that info, but if I were to guess I'd say maybe one of the sheets you are trying to set a "sh" variable to either doesn't exist or the sheet name is misspelled.

 

[Fluff]

Is that code in a regular module, or is it in a sheet module or the Thisworkbook module?
If it's not in a regular module I'd suggest you move it to one. failing that in the VBE select Tools > general > & check "Break in Class module" > OK
Then try running your code again.

 

[juneau730]

JoeMo, you were correct. After closer inspection and a good nights rest, I caught that the sheet names were mis-typed. Once that was corrected, the error went away. I have a new error now, but I know what the cause is and just have to correct that.

Thanks for the help.

 

[juneau730]

Fluff, it's in a Thisworkbook module. The error is gone, I had some typos in the sheet names so they didn't match.

 

[JoeMo]

JoeMo, you were correct. After closer inspection and a good nights rest, I caught that the sheet names were mis-typed. Once that was corrected, the error went away. I have a new error now, but I know what the cause is and just have to correct that.

Thanks for the help.

You are welcome - thanks for the reply.