loop through worksheet with different names using VBA

Question

Dim Var_1 As String
Dim Var_2 As String
Dim Var_3 As String
Dim Var_4 As String
Dim Var_5 As String
Dim Var_6 As String
Dim Var_7 As String
Dim Var_8 As String
Dim Var_9 As String
Dim Var_10 As String
Dim Var_11 As String
Dim Var_12 As String
Dim Var_13 As String
Dim Var_14 As String
Dim Var_15 As String
Dim Var_16 As String
Dim Var_17 As String
Dim Var_18 As String
Dim Var_19 As String
Dim Var_20 As String
Dim Var_21 As String
Dim Var_22 As String
Dim Var_23 As String
Dim Var_24 As String
Dim Var_25 As String
Dim Var_26 As String
Dim Var_27 As String
Dim Var_28 As String
Dim Var_29 As String

Var_1 = "Ex-Bidadi"
Var_2 = "Ex-Hospet"
Var_3 = "Ex-Chennai"
Var_4 = "Ex-Coimbatore"
Var_5 = "Ex-Gangaikondan"
Var_6 = "Ex-Pune"
Var_7 = "Ex-Goa"
Var_8 = "Ex-Mumbai"
Var_9 = "Ex-Nashik"
Var_10 = "Ex-Aurangabad"
Var_11 = "Ex-Goblej"
Var_12 = "Ex-Hyderabad"
Var_13 = Ex - Vizag
Var_14 = "Ex-Vijayawada"
Var_15 = "Ex-Chittoor"
Var_16 = "Ex - Siliguri"
Var_17 = "Ex-odhisha"
Var_18 = "Ex-Jharkhand"
Var_19 = "Ex-Bihar"
Var_20 = "Ex-NorthEast"
Var_21 = "Ex-Delhi"
Var_22 = "Ex-Udaipur"
Var_23 = "Ex-Jammu"
Var_24 = "Ex-Haridwar"
Var_25 = "Ex-Dasna"
Var_26 = "Ex-Kanpur"
Var_27 = "Ex-Unnao"
Var_28 = "Ex-Var_anasi"
Var_29 = "Ex-Bhopal"

I am showing you a part of my code and i just want to known how to loop these I tried using this representation

For n = 1 to 29
    For i = 3 To 445
        For m = 28 To 40
            ActiveWorkbook.Sheets("Var_" & n).Cells(i, m) = 999999
            least(i, m, n) =  ActiveWorkbook.Sheets("Var_" & n).Cells(i, m)
        Next m
    Next i
Next n

I have defined the least(i,m,n) array but the loop is showing an error at the subscript is out of Range I have tried using all the possibilities but the loop doesn't work

This issue lies with `ActiveWorkbook.Sheets("Var_" & n)` - this will look for a worksheet literally named "Var_1", "Var_2" etc. and not the strings you've populated these variables with — Jordan, Jun 27 '17 at 12:02
How is your data arranged on the sheet? And what are you trying to accomplish? — danieltakeshi, Jun 27 '17 at 12:03
You need to set up Var as an array, not individually numbered string variables. — CLR, Jun 27 '17 at 12:03
https://stackoverflow.com/questions/38254337/how-to-create-dynamic-variable-names-vba — Siddharth Rout, Jun 27 '17 at 12:19
https://stackoverflow.com/questions/20575866/excel-vba-variable-variable-names — Siddharth Rout, Jun 27 '17 at 12:19
If you search for `VBA Variable Variable` you will get lot of matches — Siddharth Rout, Jun 27 '17 at 12:19

score 1 · Accepted Answer · answered Jun 27 '17 at 12:09

You need to store the sheet names in an array:

Var = Array("Ex-Bidadi", "Ex-Hospet", "Ex-Chennai", "Ex-Coimbatore", "Ex-Gangaikondan", "Ex-Pune", "Ex-Goa", "Ex-Mumbai", "Ex-Nashik", "Ex-Aurangabad", "Ex-Goblej", "Ex-Hyderabad", "Ex - Vizag", "Ex-Vijayawada", "Ex-Chittoor", "Ex - Siliguri", "Ex-odhisha", "Ex-Jharkhand", "Ex-Bihar", "Ex-NorthEast", "Ex-Delhi", "Ex-Udaipur", "Ex-Jammu", "Ex-Haridwar", "Ex-Dasna", "Ex-Kanpur", "Ex-Unnao", "Ex-Var_anasi", "Ex-Bhopal")

Then inside your loop use the following:

least(i, m, n) = ActiveWorkbook.Sheets(Var(n)).Cells(i, m)

score 1 · Answer 2 · answered Jun 27 '17 at 12:12

Create an array of your sheet

Dim Var
Var = Array("Ex-Bidadi"", "Ex-Hospet", "Ex-Chennai" ... till the last sheet)

Count the number of sheets that included on your array. Array starts at index 0.

For n = 0 to 28 '29 sheets less 1, because of the array index 0
     For i = 3 To 445
         For m = 28 To 40
             ActiveWorkbook.Sheets(Var(n)).Cells(i, m) = 999999
             least(i, m, n) =  ActiveWorkbook.Sheets(Var(n)).Cells(i, m)
         Next m
     Next i
Next n

Thank You for investing your precious time – Parimal Bhalerao Jun 27 '17 at 12:35 — Parimal Bhalerao, Jun 27 '17 at 12:35

score 1 · Answer 3 · answered Jun 27 '17 at 12:28

Loop through all sheets and pick just the ones you need in a SELECT CASE statement:

Sub Test()
    Dim wrkSht As Worksheet
    Dim i As Long, m As Long
    Dim cLeast As Collection

    Set cLeast = New Collection
    For Each wrkSht In ThisWorkbook.Worksheets
        'NB:  If you want sheets that start with "Ex-" use commented lines instead:
        'Select Case Left(wrkSht.Name, 3)
        Select Case wrkSht.Name
            'Case "Ex-"
            Case "Ex-Bidadi", "Ex-Hospet", "Ex-Chnnai"
                For i = 3 To 445
                    For m = 28 To 40
                        cLeast.Add wrkSht.Cells(i, m), wrkSht.Name & "|" & i & "|" & m
                    Next m
                Next i
        Case Else
          'Code if not the sheet you're after.
        End Select
    Next wrkSht

    Debug.Print cLeast("Ex-Bidadi|3|28")

End Sub

NB: I've used a collection in the loop as not sure what you're after. Dictionaries are probably the better way to go.

Thank you for investing your precious time – Parimal Bhalerao Jun 27 '17 at 12:36 — Parimal Bhalerao, Jun 27 '17 at 12:36

loop through worksheet with different names using VBA

3 Answers3