Execute submacro a set number of times until criteria is met

[HTSCF Fareha]

I was wondering if there was a way to execute a sub on each worksheet inside the same workbook, whose worksheets were consecutively named, until the final worksheet was reached, at which point the sub would exit.

I was thinking something like the following, although I could be way off!

Option Explicit

Sub TestSub()

    Dim ws     As Worksheet
    Dim i      As Integer


    For i = 1 To 6
        Do Until i = 6
            ' Total of 6 consecutive worksheets to work on, TestData1 through TestData6
            Application.ScreenUpdating = False
            
            ThisWorkbook.ws("TestData").Name (i)
            
            
            
            ' ..... Other bits of code here
            
            
            i = i + 1
        Loop
        Application.ScreenUpdating = True
    Next i
End Sub

[Bob Phillips]

Something like this?

Sub TestSub()
Dim ws     As Worksheet
Dim i      As Long
        
    Application.ScreenUpdating = False

    With ThisWorkbook
	
        For Each ws in .Worksheets

            ' ..... Other bits of code here
	Next ws
    End With

    Application.ScreenUpdating = True
End Sub

[HTSCF Fareha]

Thanks, Bob!

I can see that this will work its way through worksheets, but cannot see how this will increment them until a fixed number is arrived at?

[Paul_Hossler]

Thanks, Bob!

I can see that this will work its way through worksheets, but cannot see how this will increment them until a fixed number is arrived at?

The

For Each ws In .Worksheets

will run on all worksheets in ThisWorkbook in the order that they are stored in Excel, which might not be TestData1, TestData2, ...



If you have more than the ones that you want, you'll need something like

Option Explicit


Sub Demo()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            If Left(ws.Name, 4) = "Test" Then
                MsgBox ws.Name
            End If
        End If
    Next
End Sub

[HTSCF Fareha]

Paul, thanks for explaining this for me. Not forgetting Bob for his original answer!