Change this line of code

[mahmed1]

Hi all

I have created a loop to go though all sheets except a few
but im concerned that more sheets might get added where ill need to ignore them so i have created a range on my refsheet that list all the sheetnames i want to loop through and NOT avoid

how do i change this line of code:

dim ws as worksheet

for each ws in thisworkbook.workheets
if ws.name <>”sheetname” and ws.name <> “sheetname2” then
’do something with that worksheet
A = ws.name ‘etc
end if
next ws

change to

for each ws in array(SheetLookupRange)
A = ws.name etc
next ws

this way i can always loop through sheets that i want that are listed in my sheetlookup range

 

[Marcelo Branco]

Maybe...

Dim rCell As Range, ws As Worksheet

For Each rCell In Range("SheetLookupRange")
    Set ws = Sheets(rCell.Value)
    With ws
        'Do something with ws
    End With
Next rCell

M.

 

[iliace]

I generally like to list the sheets I want to exclude. So, I will either create a separate sheet called ExclusionList, or an array string of sheet names - really depending on how often I add or modify the exclusion list.

For example, I might have

msEXCLUSION_SHEET_LIST As String = "Sheet 1;Sheet 2;Sheet 3"

Private Function vGetExclusionList() As Variant
  vGetExclusionList = Split$(msEXCLUSION_SHEET_LIST, ";"))
End Function

Private Sub Example()
  Dim i As Long
  Dim j As Long
  Dim bExclude as Boolean
  
  Dim wsh As Excel.Worksheet

  For Each wsh In ThisWorkbook.Worksheets
    For i = lbound(vGetExclusionList) to UBound(vGetExclusionList)
      If wsh.Name = vGetExclusionList(i) Then
        bExclude = True
        Exit For
      End If
    Next i
    If not bExclude Then
      Debug.Print wsh.Name
    End If
  Next wsh
End Sub

Or else you put them in a named range, and read them at runtime. The end result is the same.

 

[mahmed1]

Thank you