Excel VBA 在 50 张纸上运行宏

Question

在 excel VBA 中，我正在尝试在 50 张纸上运行一个简单的自动过滤器，但是，它只在一张纸上运行它。谁能帮忙？代码如下：

Sub Macro2()
'
' Macro2 Macro
'
Dim wb As Workbook
For Each wb In Application.Workbooks
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Selection.End(xlToLeft).Select
Range("G1").Select
ActiveSheet.Range("$A$1:$AC$91").AutoFilter Field:=7, Criteria1:=Array("11" _
, "21", "22", "23", "31-33", "42", "44-45", "48-49", "51", "52", "53", "54", "55", "56", "61" _
, "62", "71", "72", "81"), Operator:=xlFilterValues
Selection.End(xlToLeft).Select
Next wb
End Sub

Answer 1

你的代码很接近，但是...您正在遍历application中的每个workbook。相反，您希望遍历workbook中的每个worksheet。此外，您在这里拥有所有这些随机.Select代码。你不需要它。您只需要在每个工作表中自动过滤一个范围：

Sub Macro2()
'
' Macro2 Macro
'
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets        
ws.Range("A1:AC91").AutoFilter Field:=7, Criteria1:=Array("11" _
, "21", "22", "23", "31-33", "42", "44-45", "48-49", "51", "52", "53", "54", "55", "56", "61" _
, "62", "71", "72", "81"), Operator:=xlFilterValues        
Next ws
End Sub

现在很好，很简单，因为每行都很清楚。 1(循环浏览当前工作簿中的每个工作表 (thisworkbook(。2( 将自动过滤器应用于A1:AC91。

Answer 2

现在，您有一个循环访问所有打开的工作簿的For Each循环。我不确定这是否是您真正想要的，或者是否是尝试将您的逻辑应用于单个工作簿的所有工作表，但无论如何，Workbook有一个名为Worksheets的集合，您可以以相同的方式迭代。然后在该循环中，您只需引用工作表循环变量而不是ActiveSheet。例如：

' Here's your existing loop, which I don't know if you really want to keep or not.
For Each wb In Application.Workbooks
' Here's a loop that will access every sheet within the 'wb' workbook.
For Each ws In wb.Worksheets
' Do your stuff here.
ws.Cells(2, 2).Value = "Hello!"
Next ws
Next wb