title: How do I loop through an Autofilter using VBA in excel?
body: <p>Here is an example of the two sheets I am working with in excel:</p>
  
  <p>Sheet A (Columns A-P):</p>
  
  <pre><code>Loc_ID     Loc_Name        First     Last     ...   ...   ...
  123456     ABXC - Sales    John      Smith
  123456     ABXC - Sales    Joe       Smith
  123456     ABXC - Sales    Larry     Smith
  123456     ABXC - Sales    Carolyn   Smith
  654321     ABXC - Sales    Laura     Smith
  654321     ABXC - Sales    Amy       Smtih
  </code></pre>
  
  <p>Sheet B (Columns A-Z -- each acronym has at least 1 Loc_ID and can have up to 25):</p>
  
  <pre><code>ABC     CBA     AAU     ...   ...   ...  ...
  123456  423656  123578
  654321  656324  875321
          123987  108932
                  ...
  </code></pre>
  
  <p>In the code below, I am first looking through the acronyms in Sheet B to create a new sheet for each acronym (rename it as that acronym) and add it's locations data from Sheet A. </p>
  
  <p>Below <code>r=1</code>, I have a recorded a Macro to do what I want to accomplish for one acronym and it's locations, but for the other acronyms and its locations, I'm not sure what I can do to loop through "Sheet B" to accomplish the same task as I have done below for the acronym: "ABC".</p>
  
  <p>Anyone have a solution to this problem?</p>
  
  <pre><code>Sub Macro5()
         Dim shtA As Worksheet     'variable represents Leavers'
         Dim shtB As Worksheet     'variable represents Tables'
         Dim shtNew As Worksheet   'variable to hold the "new" sheet for each acronym'
         Dim acronyms As Range     'range to define the list of acronyms'
         Dim cl As Range           'cell iterator for each acronmym'
         Dim r As Integer          'iterator, counts the number of locations in each acronym'
         Dim valueToFind As String 'holds the location that we're trying to Find'
         Dim foundRange As Range   'the result of the .Find() method'
         Dim MyRange As Range
  
  
  'Assign our worksheets variables'
         Set shtA = Worksheets("Leavers")
         Set shtB = Worksheets("Tables")
  
  'Assign the list of acronmys in "Tables"'
         Set acronyms = shtB.Range("B1:Z1")
  
  'Loop over each DIV code:'
         For Each cl In acronyms.Cells
  'Add new sheet for each acronym:'
         Set shtNew = Sheets.Add(After:=Sheets(Sheets.Count))
         shtNew.Name = cl.Value
  
  'Start each acronym at "1"'
         r = 1
  
  Sheets("Tables").Select
  Range("B2").Select
  Selection.Copy
  Sheets("Leavers").Select
  ActiveSheet.Range("$A$1:$P$6463").AutoFilter Field:=1, Criteria1:="687987"
  Range("A1").Select
  Range(Selection, Selection.End(xlDown)).Select
  Range(Selection, Selection.End(xlToRight)).Select
  Selection.Copy
  Sheets("ABX").Select
  ActiveSheet.Paste
  Sheets("Tables").Select
  Range("B3").Select
  Selection.Copy
  Sheets("Leavers").Select
  ActiveSheet.Range("$A$1:$P$6463").AutoFilter Field:=1, Criteria1:="004740"
  ActiveCell.Offset(1, 0).Select
  With ActiveSheet.UsedRange
  Set MyRange = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1))
  MyRange.Select
  End With
  Selection.Copy
  Sheets("ABX").Select
  Range("A2").Select
  Selection.End(xlDown).Select
  ActiveCell.Offset(1, 0).Select
  ActiveSheet.Paste
  Next
  
  End Sub
  </code></pre>