Macro to move data based on multiple filters in one column

[akg742]

Hi! I'm trying to write code that will filter column J in a spreadsheet for "abc" and "def" and move the rows containing either to a different worksheet (wsOut). The code below works for "ABC" but I haven't found a way to modify the part in blue that works.

m = wbSource.Range("J" & wbSource.Rows.Count).End(xlUp).Row
On Error Resume Next
wbSource.Range("A1:K1").AutoFilter Field:=10, Criteria1:="ABC*"
With wbSource.Range("J2:J" & m).SpecialCells(xlCellTypeVisible).EntireRow
.Copy Destination:=wsOut.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With
For Each ws In Worksheets ' Clear all filters
If ws.AutoFilterMode = True Then
Debug.Print ws.Name
ws.AutoFilterMode = False
End If
Next

My next step is hopefully going to be multiple filters in multiple columns so any help with that would be appreciated. My end goal is to sort all of my data into buckets but I'm still figuring out what the rules are (new job, still training. My predecessor did this line by line which is crazy).

Thanks in advance. Let me know if more information is needed.

 

[iggydarsa]

wbSource.Range("A1:K1").AutoFilter Field:=10, Criteria1:="ABC*", Operator:=xlOr, Criteria2:="DEF*"

 

[akg742]

Thank you so much, iggydarsa! That worked perfectly and will save me so much time going forward.

 

[akg742]

I'm back. The code above worked perfectly for my first 3 criteria but, when I added more, it just moved everything to my wsOut sheet (including my header row). Is there a limit? This is what I have:

m = wbSource.Range("J" & wbSource.Rows.Count).End(xlUp).Row
On Error Resume Next
wbSource.Range("A1:K1").AutoFilter Field:=10, _
Criteria1:="cnv*", Operator:=xlOr, _
Criteria2:="cork*", Operator:=xlOr, _
Criteria3:="(E)*" , Operator:=xlOr, _
Criteria4:="Inst*", Operator:=xlOr, _
Criteria5:="IRL*", Operator:=xlOr, _
Criteria6:="JR-*", Operator:=xlOr, _
Criteria7:="kz*", Operator:=xlOr, _
Criteria8:="nv-*", Operator:=xlOr, _
Criteria9:="mah-*", Operator:=xlOr, _
Criteria10:="(s)*", Operator:=xlOr, _
Criteria11:="saz*", Operator:=xlOr, _
Criteria12:="sgs*", Operator:=xlOr, _
Criteria13:="t&e*", Operator:=xlOr, _
Criteria14:="te-*"
With wbSource.Range("J2:J" & m).SpecialCells(xlCellTypeVisible).EntireRow
.Copy Destination:=wsOut.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With
For Each ws In Worksheets ' Clear all filters
If ws.AutoFilterMode = True Then
Debug.Print ws.Name
ws.AutoFilterMode = False
End If
Next

 

[iggydarsa]

Put your own values inside Array

wbSource.Range("A1:K1").AutoFilter Field:=10, Criteria1:=Array("Text1", "Text2", "Text3", "Text4"), Operator:=xlFilterValues

 

[akg742]

Put your own values inside Array

wbSource.Range("A1:K1").AutoFilter Field:=10, Criteria1:=Array("Text1", "Text2", "Text3", "Text4"), Operator:=xlFilterValues

Thanks for the suggestion. I used your code (see below) but the end result is that only the header row from wbSource gets copied to wbOut and nothing seems to happen with my filters.

m = wbSource.Range("J" & wbSource.Rows.Count).End(xlUp).Row

On Error Resume Next
wbSource.Range("A1:K1").AutoFilter Field:=10, _
Criteria1:=Array("ang*", "anngrove*", "bp *", "cnv*", "cork*", "(E)*", "Inst*", "irl*", _
"jr-*", "kz*", "nv-*", "mah-*", "(s)*", "saz*", "sgs*", "t&e*", "te-*"), Operator:=xlFilterValues

' copy only visible rows (excluding the header row)
m = wbSource.Range("J" & wbSource.Rows.Count).End(xlUp).Row
With wbSource.Range("A2:K" & m).SpecialCells(xlCellTypeVisible).EntireRow
.Copy Destination:=wsOut.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With

For Each ws In Worksheets ' Clear all filters
If ws.AutoFilterMode = True Then
ws.AutoFilterMode = False
End If
Next