Excel VBA - Subscript out of range - Sheets name as a variable

Question

I am trying to work on a Worksheet whose name is a variable.

I have a main sheet, called "data" where I go to catch a list of names of existing sheets.

My code is as follows :

Dim data as Worksheet
dim sheet_name as String
Dim i as Integer
Set data = ThisWorkbook.Sheets("Data")

For i = 2 to 10
    sheet_name = data.Range("A"&i).Value
    With ThisWorkbook.Sheets(sheet_name)
        'Operations on the worksheet
    End With
Next i

The error prompted is "Runtime Error 9 : Subscript Out of Range" for the specific line :

With This Workbook.Sheets(sheet_name)

It is as if the object Sheets didn't understand the string sheet_name. The Sheet "sheet_name" exists for sure, I double-checked.

Unfortunately, I cannot call the sheet by its name because I have too many sheets to operate on, this is why I wanted to do a loop.

I tried not working with the "With" clause but just referring to every object of the sheets with "ThisWorkbook.Sheets(sheet_name) in front but doesn't work either.

Do you know if it is possible to call a string variable inside a Sheets()?

Thanks a lot for your help !

Kind regards,

It's possible to do what you want - it looks like there is no sheet matching the value of sheet_name though. — Tim Williams
Your code definitely works provided the list of names on sheet Data exactly match the sheet names (they are case insensitive). Check for things like extra spaces on the beginning or end of the names (both in data and actual sheet names). Any blanks in Data!A2:A10 will also cause this error. — chris neilsen

Shai Rado Shai Rado · Accepted Answer · 2017-03-30T02:42:39

The reason for your error was given in the comments above by @chris neilsen

You could use the code below to check or avoid having these kind of errors:

Option Explicit

Sub CheckShtExists()

Dim data As Worksheet
Dim sheet_name As String

Set data = ThisWorkbook.Sheets("Data")

Dim ws As Worksheet
Dim ShtNamesArr() As String
Dim i As Long

ReDim ShtNamesArr(0 To ThisWorkbook.Worksheets.Count - 1) ' resize array to number of worksheets in This Workbook

' loop thourgh all worksheets and store their names in array
For Each ws In ThisWorkbook.Worksheets
    ShtNamesArr(i) = ws.Name
    i = i + 1
Next ws

For i = 2 To 10
    If data.Range("A" & i).Value <> "" Then ' ignore blank cells
        sheet_name = data.Range("A" & i).Value
        If Not IsError(Application.Match(sheet_name, ShtNamesArr, 0)) Then ' use Application.Match to see there is a sheet with this name
            With ThisWorkbook.Sheets(sheet_name)
                'Operations on the worksheet
            End With
        Else ' No Match
            MsgBox sheet_name & " doesn't exists in your workbook"
        End If
    End If
Next i

End Sub

Excel VBA - Subscript out of range - Sheets name as a variable

1 Answers