CODEWITHSUNDEEP
excelvbaworksheet
chrisphils26
chrisphils26

Reputation: 39

Using VBA to Loop Through Sheets with a Specific Name

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

Upvotes: 1

Views: 3438

Answers (3)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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

Upvotes: 2

Teamothy
Teamothy

Reputation: 2016

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

Upvotes: 3

Pablo G
Pablo G

Reputation: 199

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

Upvotes: 0

Related Questions