Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excluding some sheets from a loop in VBA

0

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? 

Answer
Discuss

Discussion

If it is only running on the active sheet, like looping through cells, then it won't ever do anything with the other sheets. Or do you mean that you are looping through all of the worksheets in the workbook and want to make sure that you exclude specific sheets? If so, that's just a simple IF check against the worksheets name and I can give you a code sample for that. If neither is what you are doing, just edit your question and explain it more or provide a sample file or code.
don (rep: 1989) Dec 2, '20 at 6:25 pm
Add to Discussion

Answers

0
Selected Answer

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.

Discuss

Discussion

Very neat @Variatus! 

Sam. don't forget to have the Immediate Window open in VB Explorer so you can see the outcome of Debug.Print via either method (realising after testing the code, that line will be replaced by the things you want to do to several sheets)
John_Ru (rep: 6102) Dec 3, '20 at 3:57 am
Thank you so much for such a great guideness.
sam (rep: 4) Dec 3, '20 at 1:10 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login