CODEWITHSUNDEEP
excelvba
vini
vini

Reputation: 15

Exclude worksheets from loop with a list

I am running a loop through all my worksheets to collcet data on the first worksheet. Now I want to exclude a list of worksheets that is defines by their namees in a list on the first worksheet.

I could exclude them one by one like this:

dim ws as worksheet
For each ws in ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" and ws.name <> "Sheet2"

and so on.

BUT Since the data will change in the future I dont want to edit this code everytime the "exclude-list" changes.

UPDATE I have tried the solution from "CLR" because it seemed like an easy way to fix it and it worked for my case. Just a reminder: You also have to include the name of the worksheet you want to diaplay your data on in the list and you are used to the If ws.Name <> "Sheet1" and ws.name <> "Sheet2" method. All the other solutions might work too, when I have the time I might test them, too.

Upvotes: 1

Views: 80

Answers (3)

VBasic2008
VBasic2008

Reputation: 54817

Loop Through Worksheets Excluding the Ones in a List

  • Test this code as-is before adding your processing code assuring yourself that it works correctly i.e. that it includes the correct worksheets.
Option Explicit

Sub CollectData()

    ' Define constants.
    Const LIST_WORKSHEET_ID = 1 ' rather use the (tab name), e.g. "Sheet1"
    Const LIST_FIRST_CELL As String = "Z2"

    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the list range.
    Dim lws As Worksheet: Set lws = wb.Sheets(LIST_WORKSHEET_ID)
    Dim lfCell As Range: Set lfCell = lws.Range(LIST_FIRST_CELL)
    Dim llCell As Range
    Set llCell = lws.Cells(lws.Rows.Count, lfCell.Column).End(xlUp)
    Dim lrg As Range: Set lrg = lws.Range(lfCell, llCell)
    
    Dim sws As Worksheet
    
    ' Loop through all worksheets...
    For Each sws In wb.Worksheets
        ' ... excluding the ones from the list:
        If IsError(Application.Match(sws.Name, lrg, 0)) Then
            
            ' Continue using the 'sws' variable , e.g.:
            Debug.Print sws.Name
        
        'Else ' it's one from the list; do nothing
        End If
    Next sws
    
    MsgBox "Data collected.", vbInformation
    
End Sub

Upvotes: 0

CLR
CLR

Reputation: 12279

Process worksheets of active workbook, excluding those on a list in a specified range:

Modify the A1:A6 address to the location of your exclusion list. Modify the Sheet1 part if your list is on another named sheet.

Sub ProcessWorksheets()
    
    'declarations
    Dim ws As Worksheet
    Dim exceptionlist As Range
    
    'define range that contains exceptions
    Set exceptionlist = ActiveWorkbook.Worksheets("Sheet1").Range("A1:A6")
    
    'cycle through each worksheet
    For Each ws In ThisWorkbook.Worksheets
        'if ws.name does not exist in the range
        If Application.CountIf(exceptionlist, ws.Name) = 0 Then
            'do something with ws
            Debug.Print ws.Name
        Else
            'ignore it, do nothing
        End If
    Next

End Sub

Upvotes: 1

Aldert
Aldert

Reputation: 4313

What you can do is create a sheet named Exclude. On column A write Exclude on first line. Your other sheets to exclude after. Finally, use code below..

Dim Lastrow As Integer
Lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

Set rs = ws.Range("A1:A1" & Lastrow)


For Each ws In ThisWorkbook.Worksheets
    If Not rs.Find(ws.Name) Is Nothing Then
        'Your code
    End If
Next

Upvotes: 0

Related Questions