Hi there
I have a data set within that data set i have a list of unique values that i want to filter on, these can be different every time.
I have written the below, which takes that column and makes it a unique list of values, i now want to filter on the data set going through each value in the list.
The unique values are in Column AQ, and i want to filter on the column AN. Can you help?
Sub RunSplitReport()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Call GetUniqueValues
Call LoopEachName
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub GetUniqueValues()
Sheets("Data").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("AN1:AN" & LR).SpecialCells(xlCellTypeVisible).Copy
Range("AQ1").PasteSpecial Paste:=xlPasteValues
LR = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("AQ1:AQ" & LR).SpecialCells(xlCellTypeVisible).RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
Sub LoopEachName()
Dim aNames As Variant, Itm As Variant
With Range("AQ1", Range("AN" & Rows.Count).End(xlUp))
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
aNames = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value
For Each Itm In aNames
.AutoFilter Field:=1, Criteria1:=Itm
'Copy and paste data'
Next Itm
.AutoFilter
End With
End Sub
I have a data set within that data set i have a list of unique values that i want to filter on, these can be different every time.
I have written the below, which takes that column and makes it a unique list of values, i now want to filter on the data set going through each value in the list.
The unique values are in Column AQ, and i want to filter on the column AN. Can you help?
Sub RunSplitReport()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Call GetUniqueValues
Call LoopEachName
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub GetUniqueValues()
Sheets("Data").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("AN1:AN" & LR).SpecialCells(xlCellTypeVisible).Copy
Range("AQ1").PasteSpecial Paste:=xlPasteValues
LR = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("AQ1:AQ" & LR).SpecialCells(xlCellTypeVisible).RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
Sub LoopEachName()
Dim aNames As Variant, Itm As Variant
With Range("AQ1", Range("AN" & Rows.Count).End(xlUp))
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
aNames = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value
For Each Itm In aNames
.AutoFilter Field:=1, Criteria1:=Itm
'Copy and paste data'
Next Itm
.AutoFilter
End With
End Sub