Multi tool use

Excel VBA running a macro across 50 sheets

In excel VBA, I am trying to run a simple autofilter across 50 sheets, however, it only runs it across one sheet. Can anyone help? The code is below:

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


{}

2 Answers
2

Right now you have a For Each loop that iterates over all open workbooks. I'm not sure if this is what you actually want or if it was an attempt to apply your logic to all sheets of a single workbook, but in any case, a Workbook has a collection called Worksheets over which you can iterate in the same way. Then inside that loop you'd just reference your worksheet loop variable instead of ActiveSheet. For instance:

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


Your code is close, but... you are iterating through every workbook in the application. Instead you want to iterate through every worksheet in the workbook. Furthermore you have all this random .Select code in here. You don't need it. You just need to autofilter a range in each worksheet:

workbook

application

worksheet

workbook

.Select

Sub Macro2()
'
' Macro2 Macro
'

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.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
Next ws
End Sub


Nice and simple now

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.