VBA Loop through list of variables

AggyRJ

New Member
Joined
Mar 29, 2013
Messages
16
I am attempting to do some type of loop where I filter a table using all the values found in column A. Each time I filter I want to copy the filtered table and paste it to another worksheet.

The data table in columns S:AJ and the list of values starts with cell A2. These are on a worksheet I have named "Summary Analysis". I would like to filter the fifth column in the table using the values from the list in column A so that I can create separate data tables on another worksheet, Data. I have written the code to do this, but I don't know how to do a loop that uses each value in the list until it's done. The number of values in the column A list will vary over time.

So far my code assigns the value from cell A2 as a variable, then filters the data in columns S:AJ for that variable. It then does a count to make sure there is anything in the resulting list and if so it copies the data and pastes it to where it needs to go in the Data worksheet. I have it set so that it will paste two rows below the last used line on the Data worksheet (there is already other data there so even the first paste should do this).

I will paste what I have so far. Any help on how I can modify this to include a loop would be great.

VBA Code:
Sub Get_Data_Tables()
    Dim LR As Long
    Dim SAC As String
    Dim SAC_Count As Long
    
    LR = Cells(Rows.Count, 19).End(xlUp).Row
    
    SAC = Range("A2").Value

        With Range("S1:AJ" & LR)
            .AutoFilter
            .AutoFilter Field:=5, Criteria1:=SAC
        End With
        
        SAC_Count = ActiveSheet.AutoFilter.Range.Columns(19).SpecialCells(xlCellTypeVisible).Count
        
        If SAC_Count = 1 Then
            With Range("S1:AJ" & LR)
             .AutoFilter
            End With
        Else
            Range("S1:AJ" & LR, Selection.End(xlDown)).Copy
            Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
        End If
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,057
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub AggyRJ()
   Dim LR As Long, i As Long
   Dim Ary As Variant
   
   LR = Cells(Rows.Count, 19).End(xlUp).Row
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   
   For i = 1 To UBound(Ary)
      With ActiveSheet
         .Range("S1:AJ" & LR).AutoFilter 5, Ary(i, 1)
         .AutoFilter.Range.Offset(1).Copy
         Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(2).PasteSpecial xlPasteValues
         .AutoFilterMode = False
      End With
   Next i
   Application.CutCopyMode = False
End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,057
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,032
Messages
5,767,739
Members
425,429
Latest member
MMMMMM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top