Using VBA to Loop Through Sheets with a Specific Name

Question

I am trying to build a macro to run through a file and open a userform for every sheet that has the name "File-n" where n is an integer. I've tried this code, but it doesn't work:

Dim WS As Worksheet
Dim indx As Integer

For Each WS In ThisWorkbook.Worksheets
    WS.Activate

    If WS.Name = "File-" & indx Then
        WS.Select
        userform1.show
    End If

Next WS

This macro doesn't recognize any sheets. The code below works, but I was hoping to clean it up if possible. I don't like listing out the potential number of sheets up to thirty.

If WS.Name = "File-0" or WS.Name = "File-1" or WS.Name = "File-2" or ... Then

Possibly an easier approach - do you have any sheets with names starting with `"File-"` that you *don't* want to consider? — BigBen, Dec 04 '19 at 15:15
@BigBen No, all sheets with the name "File-" would be relevant in this case. — chrisphils26, Dec 04 '19 at 15:18
Then just use `InStr` (see the posted answer), or `Left` even. — BigBen, Dec 04 '19 at 15:19
Try to avoid `.Activate` and `.Select` if not expressly needed for user communication - c.f. [How to avoid using Select in Excel VBA?](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=SearchResults&s=1|191.2192) — T.M., Dec 06 '19 at 14:48

score 3 · Accepted Answer · answered Dec 04 '19 at 15:15

You can use Like as @BigBen mentioned in comment or use Instr like below:

Option Explicit
Sub test()

Dim WS As Worksheet
Dim indx As Integer

For Each WS In ThisWorkbook.Worksheets
    WS.Activate

    If InStr(1, LCase(WS.Name), "file") Then
        WS.Select
        UserForm1.Show
    End If

Next WS

End Sub

shrivallabha.redij · Answer 2 · 2019-12-04T15:26:22.850

2

See if below code helps

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    WS.Activate
    If WS.Name Like "File-*" Then
        WS.Select
        userform1.show
    End If
Next WS

edited Dec 04 '19 at 15:26

answered Dec 04 '19 at 15:17

shrivallabha.redij

5,832
1
12
27

2

This would compare each Worksheet against an incremental indx number, so I doubt it would work as expected... – Xabier Dec 04 '19 at 15:21
1

@Xabier thank you for pointing this out. I see where the mistake is! I have corrected the code. – shrivallabha.redij Dec 04 '19 at 15:27

score 0 · Answer 3 · answered Dec 04 '19 at 15:15

0

Add one more loop for indx

Dim WS As Worksheet
Dim indx As Integer
For i = 1 to 30
    indx  = i
For Each WS In ThisWorkbook.Worksheets
    WS.Activate
    If WS.Name = "File-" & i Then
       userform1.show
    End If
Next WS
Next i

answered Dec 04 '19 at 15:15

Pablo G

199
12

1

If you were to do this approach, the outer loop should be the worksheet loop, and the inner loop should be `i`. But... it's really inefficient and there are way better approaches. – BigBen Dec 04 '19 at 15:47

Using VBA to Loop Through Sheets with a Specific Name

3 Answers3