Hi everyone!
I'm going to run a loop in VBA, but I don't want it runs over all other sheets. My question is how to exclude some other sheets/tabs from a loop in VBA which is runing for the current/active sheet.
Any suggested codes or advice?
Hi everyone!
I'm going to run a loop in VBA, but I don't want it runs over all other sheets. My question is how to exclude some other sheets/tabs from a loop in VBA which is runing for the current/active sheet.
Any suggested codes or advice?
The procedure below loops through all worksheets in the specified workbook, checks its name against a list and does soemthing with those pass muster.
Sub AddressSelectedSheets()
Dim Wb As Workbook
Dim Ws As Worksheet ' loop object
Dim Excl As String ' Excluded tabs
' define the workbook (you can also specify a workbook by name)
Set Wb = ThisWorkbook
' list names of tabs to be excluded, comma-sparated
' avoid blanks that aren't part of the names
Excl = "Sheet15,My other worksheet,Cockpit"
' assign each worksheet in turn to the variables Ws
For Each Ws In Wb.Worksheets
If InStr(1, "," & Excl & ",", "," & Ws.Name & ",", vbTextCompare) = 0 Then
Debug.Print "The included tab is called """ & Ws.Name & """."
End If
Next Ws
End Sub
Observe that the method is easily reversible. If (Instr([StringA], [StringB]) = 0 is the opposite of If (Instr([StringA], [StringB]) <> 0 (which is the same as If (Instr([StringA], [StringB])). So, with essentially the same code you cxan either exclude the sheets that are listed or all others. Note that vbTextCompare specifies case insensitivity. Capitalisation in the tab names need not b e observed.