HomePro
Board Regular
- Joined
- Aug 3, 2021
- Messages
- 157
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- 2003 or older
- Prefer Not To Say
- Platform
- Windows
- MacOS
- Mobile
- Web
I would like to take a worksheet and loop thru all possible filters in column "e"'s auto filters drop down box and copy all the data per filter to a new sheet named for that filter.
So if I had three items in the drop don list: [ball - bat - glove] I would now have 4 sheets. the original, one named bat with all the rows that had "Bat" in column e in a worksheet named BAT, another for balls etc....
Got so far and I am drawing a blank!
Sub Filter_E()
Dim Filtered As Range, ws As Worksheet
Set ws = ActiveSheet
Set Filtered = ws.AutoFilter.Range
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
Filtered.AutoFilter Field:=5, Criteria1:="<>x", Operator:=xlAnd
End Sub
So if I had three items in the drop don list: [ball - bat - glove] I would now have 4 sheets. the original, one named bat with all the rows that had "Bat" in column e in a worksheet named BAT, another for balls etc....
Got so far and I am drawing a blank!
Sub Filter_E()
Dim Filtered As Range, ws As Worksheet
Set ws = ActiveSheet
Set Filtered = ws.AutoFilter.Range
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
Filtered.AutoFilter Field:=5, Criteria1:="<>x", Operator:=xlAnd
End Sub