Subscript out of Range error trying to activate worksheets from an array

[mikenelena]

The following code stores various worksheets in an array and calls another module (working fine) that sends a copy of the worksheets through Outlook, to recipients. My worksheet names are numbers. They all exist, and I can see them listed in the array in the Locals window. The first sheet, 1105, shows as a variable value when I hover of the variable name.

For some reason I'm getting a subscript out of range error (ThisWorkbook.Sheets(sheetName).Activate), as if the worksheets don't exist. I've verified the names in the properties window. Why does this error persist, and how can I resolve this? Thanks in advance for any help folks can offer.

Here is the code:

Sub FindMatch2()
Dim wsMain As Worksheet, wsFileData As Worksheet
Dim i As Long, j As Long
Dim matchFound As Boolean, arrCounter As Long
Dim matchDate As Date
Dim arrSheets() As Variant
Dim sheetName As Variant

Set wsMain = ThisWorkbook.Sheets("Main Menu")
Set wsFileData = ThisWorkbook.Sheets("File Data")

matchDate = wsFileData.Range("N1").Value

'Iterate through the cells in Row 2 of the Main Menu worksheet
For i = 1 To wsMain.Cells(2, Columns.Count).End(xlToLeft).Column
    If wsMain.Cells(2, i).Value = matchDate Then
        matchFound = True
        'Iterate through the cells in the found column
        For j = 3 To wsMain.Cells(Rows.Count, i).End(xlUp).Row
            If wsMain.Cells(j, i).Value > 0 Then
                'Get the sheet name from column C
                ReDim Preserve arrSheets(0 To arrCounter)
                arrSheets(arrCounter) = wsMain.Cells(j, 2).Value
                arrCounter = arrCounter + 1
            End If
        Next j
    End If

    If matchFound Then
        Exit For
    End If
Next i

''Activate each sheet in the array and run the Mail_ActiveSheet routine
If arrCounter > 0 Then
    For Each sheetName In arrSheets
        If Not ThisWorkbook.Sheets.Item(sheetName) Is Nothing Then
            ThisWorkbook.Sheets(sheetName).Activate
            Call Mail_ActiveSheet
        End If
    Next sheetName
    MsgBox "Sheets with values greater than 0: " & Join(arrSheets, ", ")
Else
    MsgBox "No sheets found with values greater than 0."
End If

End Sub

 

[Legacy 143009]

I guess it is because Sheets accepts argument as string type and you are trying to set a variable as number. Try:

ThisWorkbook.Sheets(CStr(sheetName)).Activate

 

[mikenelena]

Flashbond, that worked!! I can't thank you enough! I was pulling my hair out over this one today!

 

[Legacy 143009]

Gald it did work! Thanks for the feedback