根据变量vba的命名范围定义数组

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

我有下面的VBA，它循环遍历数组()公式中的所有命名选项卡。我希望它是一个动态范围，它不会将值输入到数组()中，而是引用具有这些值的工作表中的一个范围，当我添加更多值时，它是动态的

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!

谢谢!

1 个解决方案

#1

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

如果表名列表存储在从A1开始的工作表“Config”中:

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