loop through specific sheets

[ammartino44]

Hello. How do loop through specific sheets?

I saw this, but I want to select something easily, like sheets 5 through 15, for example.

Sub loopthrusheetarray()
Dim Sh As Worksheet
For Each Sh In Sheets(Array("Sheet2", "Sheet6", "Sheet10"))
MsgBox Sh.Range("a1")
Next

[TMS]

Some options:

Option Explicit

Sub sDisplaySheetNames()

Dim i As Long
Dim sh As Worksheet
Const lFSNo As Long = 5
Const lLSNo As Long = 15

For i = lFSNo To lLSNo
    Debug.Print "1 " & Sheets("Sheet" & i).Name
Next i

For i = lFSNo To lLSNo
    Debug.Print "2 " & Sheets(i).Name
Next i

For Each sh In ThisWorkbook.Worksheets
    If Replace(sh.CodeName, "Sheet", "") >= lFSNo _
    And Replace(sh.CodeName, "Sheet", "") <= lLSNo _
    Then
        Debug.Print "3 " & sh.Name
    End If
Next sh

End Sub

Regards, TMS

[berlan]

Alternatively from Sheet5 to Sheet15,

Sub test()
    Dim ws As Worksheet
    For Each ws In Sheets(["Sheet"&transpose(row(5:15))])
        Debug.Print ws.Name
    Next
End Sub

based on sheetname.

or,

Sub test2()
    Dim ws As Worksheet
    For Each ws In Sheets([transpose(row(5:15))])
        Debug.Print ws.Name
    Next
End Sub

based on sheets by index number 5 to 15.


@TMS, I like your approach on using CodeName.

[TMS]

@berlan: mutual appreciation ... I like your alternatives too