Define Array by named range for variant vba

Question

I have the below VBA, which loops through all the named tabs in the array() formula. I want that to be a dynamic range that, instead of having the values typed into the array(), it references a range in a worksheet that has those values, and it dynamic when I add more

Sub Filter_To_Send()

Dim WshtNames As Variant
Dim WshtNameCrnt As Variant

WshtNames = Array("RASC", "RSUV", "SBKP", "RGRN", "SBTK", "RWAS", "RABT", "RHAY", "RLEX", "RARM", "RBCK", "RBER", "RCCP", "RDAL", "RHEB", "RSST", "RMST", "RMIA", "REHV", "RNBY", "RNOF", "RUTC", "RSCS", "RSJO", "RSCQ", "RSHA", "RAWP", "SART", "SASK", "SALC", "SNHQ", "SOEX", "SPHL", "SSHP", "SMRN")

For Each WshtNameCrnt In WshtNames
With Worksheets(WshtNameCrnt)
    .Range("A5").AutoFilter field:=16, Criteria1:="<>0"
End With
Next WshtNameCrnt

End Sub

Thanks!

Tim Williams · Accepted Answer · 2016-09-21 19:59:24Z

1

If the list of sheet names is stored on the worksheet "Config" beginning at A1:

Sub Filter_To_Send()

    Dim c As Variant
    Dim rngSheets As Range

    With Sheets("Config")
        Set rngSheets = .Range(.Range("A1"), .Cells(.Rows.Count, 1).End(xlUp))
    End with


    For Each c In rngSheets.Cells
        With Worksheets(c.Value)
            .Range("A5").AutoFilter field:=16, Criteria1:="<>0"
        End With
    Next c

End Sub

answered Sep 21, 2016 at 19:59

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dylan Kindler Over a year ago

@tim.williams -- thanks so much. I get run time error '9' - subscript out of range, when it gets to the "With Worksheets(c.Value)" line.

Tim Williams Over a year ago

That would indicate there's no worksheet with that name in the active workbook. Does your list of worksheets have a header? If Yes then change that .Range("A1") to .Range("A2")

Dylan Kindler Over a year ago

Got it. I tried to add the list of tabs to an existing tab and move it from A1 to where the list started. But, sounds like it's easier to have its own dedicated tab. Thanks so much!!

Tim Williams Over a year ago

It can be anywhere - as long as it's in its own column with nothing below it, so the code can find the last populated value using End(xlUp)

Collectives™ on Stack Overflow

Define Array by named range for variant vba

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related