VBA Filter on column

[cynthi263]

Hi, I am trying to have the macro filter out a list of items i have in a specific column (there are over 600 items), copy that info and paste it in another cell. I've tried it out using a handful of items and it worked but when i pasted the entire list i kept getting errors. Since there are so many, could i have the Macro look at the list on another tab (product ids) and do it that way instead of having to enter all the items in the VBA page?

Sub NM()
Set wb = ThisWorkbook

Set sh1 = wb.Sheets("OL Merged New")
Set sh2 = wb.Sheets("NM IDs")
Set sh3 = wb.Sheets("Prod IDs")

'filter nm ids and move to nm id tab

sh1.Range("A2:AB3000").AutoFilter Field:=16, Criteria1:=Array("1", "2", "3", "4", ETC), Operator:=xlFilterValues

sh1.Range("A2:AB3000").SpecialCells(xlCellTypeVisible).Copy
With sh2
sh2.Cells(2, 1).PasteSpecial xlPasteAll
End With
sh1.Range("A2:AB3000").Offset(1, 0).EntireRow.Delete

sh1.Range("A2:AB3000").AutoFilter Field:=16


End Sub

 

[Fluff]

How about

Ary = sh3.Range("A2", sh3.Range("A" & Rows.Count).End(xlUp))
sh1.Range("A2:AB3000").AutoFilter Field:=16, Criteria1:=Application.Transpose(Ary), Operator:=xlFilterValues

 

[cynthi263]

t

How about

Ary = sh3.Range("A2", sh3.Range("A" & Rows.Count).End(xlUp))
sh1.Range("A2:AB3000").AutoFilter Field:=16, Criteria1:=Application.Transpose(Ary), Operator:=xlFilterValues

That did it! thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[cynthi263]

Hello, regarding the same macro - i have the below and noticed that the line right under the header when autofiltered is not being deleted, causing a duplication of the line?

 

[Fluff]

Is your header in row 1 or row 2?

 

[cynthi263]

Is your header in row 1 or row 2?

row 1

 

[Fluff]

In that case change the A2 in the range to A1

 

[cynthi263]

In that case change the A2 in the range to A1

that worked, thanks again!

 

[Fluff]

Glad to help & thanks for the feedback.