Excel VBA running a macro across 50 sheets

2
votes

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
vbaexcel
You can just paste your code in as-is, highlight the entire code block, then hit the little {} button at the top. It will indent each line with 4 spaces and it will appear as code after submitting the post. – JNevill
you need to go through each worksheet, you are going through each workbook in your workbook (which is 1). You would be looking at more of a file folder path if you wanted to cycle through 50 workbooks. – Wookies-Will-Code

2 Answers

3
votes

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:

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

Nice and simple now as each line is clear. 1) Loop through each worksheet in the current workbook (thisworkbook). 2) Apply an autofilter to A1:AC91.

1
votes

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:

' 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