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.
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