How do I loop a macro through a named array of worksheets in a workbook

Question

Summary: looping a macro through a specific array of ws. Array's name being defined in name manager. Somehow the macro loops through an additional ws which is NOT in the array.

Aim was to replace a long macro call sub for every ws I need to run the code through and learn how to use a named range array as they are dynamic.

Code is:(found it on stackoverflow then tweaked it to my purpose)

    Public Sub Tester02()
    Dim SH As Worksheet
    Dim rng As Range
    Dim rCell As Range

    Set rng = 
    ActiveWorkbook.Sheets("ListOfSheets").Range("ListOfWorksheetsHRIB")

    For Each SH In ActiveWorkbook.Worksheets
    If Not IsError(Application.Match(SH.Name, rng, 0)) Then
    Call SingleCtrRefreshHRIB
    End If
    Next SH
    Worksheets("Dashboard").Activate
    End Sub

For some reason the macro loops through an additional sheet (Sheet1= Dashboard).

In my untrained the macro goes to the "Dashboard" ws AFTER it has run through the named range. Therefore this worksheet should not included.

I can run the macro without activating the Dashboard and reposition the cursor through instructions. yet I would like to know why it does not work and learn how to restrict a macro to specifically a named range

No sure it is relevant but sub SingleCtrRefreshHRIB unprotects,refreshes a filter and protects the ws in the named range.

On a side note, I was surprised to see that the loop was not really more efficient (time-wise) than going through each individual ws. More elegant and obviously dynamic but not faster.

If needed code for the macro being called (I will clean the activate and select in this code as soon as I can).

    Sub SingleCtrRefreshHRIB()
    '
    ' In active IB worksheet macro unprotects, refreshes the "ACTIVE" 12 
    field and reprotects for HR
    '
    Application.ScreenUpdating = False
    ActiveSheet.Select
    ActiveSheet.Unprotect
    ActiveSheet.Range("$A$1:$AB$1051").AutoFilter Field:=12
    ActiveSheet.Range("$A$1:$AB$1051").AutoFilter Field:=12, Criteria1:= 
    _"ACTIVE"
    ActiveSheet.Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, 
    Scenarios:= _False, AllowFormattingColumns:=True, 
    AllowFiltering:=True
    End Sub

thanks!

So the problem is that `SingleCtrRefreshHRIB` is being called for `Dashboard` as well, although its name is not included in the contents of `ListOfWorksheetsHRIB` ? — Stavros Jon, Apr 28 '19 at 10:14
wow.. cant believe such a fast sign of interest.. Thanks! yes and I have run the macro without calling dashboard at the end same result — Christian Cazenave, Apr 28 '19 at 10:41
Have a look at this, may help https://stackoverflow.com/a/30605765/4961700 — Solar Mike, Apr 28 '19 at 10:53
@ChristianCazenave So, if for the sake of testing, you would replace `Call SingleCtrRefreshHRIB` with `Debug.Print SH.Name`, you would get all the names of the sheets in your console, including `Dashboard` ? — Stavros Jon, Apr 28 '19 at 11:00
Will test now. Though what I wanted to learn was to loop a macro though a named range. I could use Array ("sheet1", etc...) but wanted to learn somewhere more elegant and dynamic — Christian Cazenave, Apr 28 '19 at 11:27
Tested but nothing happened. Believe I misunderstood your suggestion. I sort of understand the Debug.Print SH.Name code but cannot see how it replaces call the main sub nor how would it avoid to loop it though all the sheet in the wb — Christian Cazenave, Apr 28 '19 at 11:34
@ChristianCazenave it was just for debugging purposes. Try my answer, after making a backup of your files. — Stavros Jon, Apr 28 '19 at 11:35

Stavros Jon · Answer 1 · 2019-04-28T13:03:13.877

The use of ActiveSheet can lead to confusion, errors and unwanted and unpredictable behavior. Please avoid using it. It's a bad practice.

Make a back up of your files and then try this on a copy:

Public Sub Tester02()
Dim SH As Worksheet 'declare a worksheet variable which can hold a worksheet object
Dim WB As Workbook 'declare a workbook variable which can hold a workbook object
Dim rng As Range 'declare a range variable which can hold a range object
Set WB = Application.Workbooks("The Name of your Workbook") 'assign a specific workbook to your workbook variable
Set rng = WB.Worksheets("ListOfSheets").Range("ListOfWorksheetsHRIB") 'assign a specific range to your range variable. Get the named range ListOfWorksheetsHRIB which belongs to the worksheet named ListOfSheets which belongs to WB and assign it to rng.

For Each SH In WB.Worksheets 'loop through all the worksheets that belong to WB
    If Not IsError(Application.Match(SH.Name, rng, 0)) Then 'check if the current worksheet's name is in the list of worksheets contained in rng
        Call SingleCtrRefreshHRIB(SH) 'if it is, pass this particular worksheet as parameter to SingleCtrRefreshHRIB
    End If
Next SH ' move on to the next worksheet and repeat
Worksheets("Dashboard").Activate 'not sure why you need this. Try avoiding .Activate. Use explicit references to worksheets.
End Sub

Sub SingleCtrRefreshHRIB(sht As Worksheet) 'This procedure gets a worksheet as a parameter and uses it to perform some actions
Application.ScreenUpdating = False
sht.Unprotect
sht.Range("$A$1:$AB$1051").AutoFilter Field:=12
sht.Range("$A$1:$AB$1051").AutoFilter Field:=12, Criteria1:="ACTIVE"
sht.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingColumns:=True, AllowFiltering:=True
Application.ScreenUpdating = True
End Sub

I need to read all the comment on Activate and select as it seems to be an issue all serious codes ( obviously not me) are referring to. Oh! now I see. Stepping into it and learning how it works. I will have a question post testing because I hate using something without udnerstading. — Christian Cazenave, Apr 28 '19 at 11:38
Olla kala philo.. It did work and I am tweaking it for another purpose. So now for the understanding part. You "declared" (sure wrong term but), the workbook as WB, told the code to "fetch" rng in in a specific sheet in WB, specific range. Where I am "dumb" is that I do not "get" when/how is SingleCtrRefreshHRIB called. At what point when it goes through the loop does SingleCtrRefreshHRIB is triggered. Stupid question I am sure but can't see the sequence. Anyhow I am grateful. Now reading on how to use explicit references. Will get there. — Christian Cazenave, Apr 28 '19 at 11:54
Ha! I see you're polishing both your VBA and Greek skills! Glad I could help. I've added some comments in my post above, that should help you understand. If this helped you, please consider upvoting. — Stavros Jon, Apr 28 '19 at 13:07
My mother is half greek half Italian... Will look at comments tonight Once again thanks!. My upvote does not count as yet as I am too much of a newbie.. But since I am going to seek further help soon I guess my contribution count will go up.. — Christian Cazenave, Apr 29 '19 at 05:26

How do I loop a macro through a named array of worksheets in a workbook

1 Answers1