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!