Looping through worksheets which are named is a relatively straight forward task. Typically you simply isolate the sheets by name and then loop through specifically named sheets. The process is all well and good as long as the sheet names remain the same. The following is a simple example of looping through named sheets.
Option Explicit
Sub Loop() 'Excel VBA to loop and only include sheets(1-3)
Dim sh As Variant
For Each sh In Array("Sheet1", "Sheet2", "Sheet3")
Sheets(sh).[b10].Interior.Color=vbYellow
Next sh
End Sub
The sheets are originally titled "Sheet" 1, 2, 3. Now if one of these sheet names change, the code will break and your users will be left scratching their heads. Not everyone has your level of dexterity when it comes to VBA.
The answer is to not loop through the sheet names but to use the worksheet code names. I have long advocated that referring to worksheet code names is the best way to refer to a worksheet in VBA. You will find dozens of examples of using the worksheet code name on this site and very few where I actually refer to the worksheet name. That is because of the point I made above - moreover if your user changes the sheet name then the code will not work as intended. Always wherever possible, refer to the worksheet code name!!! The code above is the focus of the article - it is where we can set up an intelligent loop that looks at the values in an array (memory storage).
Sub LoopCodeName()
Dim i As Long
Dim ar As Variant
ar = Array(Sheet1, Sheet2, Sheet3)
For i = 0 To UBound(ar)
If ar(i).FilterMode Then ar(i).ShowAllData
Next i
End Sub
The code does a relatively simple task of looking at each of the worksheets in the workbook, the code names (1, 2, 3) and checking if there is a filter on that sheet. If there is the filter is removed. This sort of task might be performed before filtering or copying the entire contents of a sheet. I am sure you can find a myriad of uses for this Excel VBA gem!
Take note of how the worksheets are referenced. They do not have quotation marks and the loop starts at zero. Of course we could use option base 1 to get around this but for now the loop works quite well. Happy coding.
