VBA code for copy/paste filtered table

[AiresCamoes]

Hello. I'm new to VBA and I need a code for copying and pasting some filtered rows from a table into another table. The origin sheet is "Folha3" the destination sheet is "Folha2". The origin table is "Table1_2" and the criteria for filtring are: "16311100-9",
"92620000-3",
"77320000-9",
"18412000-0",
"18412200-2",
"18820000-3",
"18932000-1",
"16150000-1",
"37400000-2",
"37410000-5",
"37412000-9",
"37452000-1",
"37450000-7",
"37451000-4",
"37453000-8",
"45112720-8",
"45212221-1",
"45212223-5",
"45212225-9",
"45242100-6",
"77320000-9"

 

[goncalogera]

Hi there Aires and welcome to the community! The criteria is in which column?

 

[AiresCamoes]

Hi there Aires and welcome to the community! The criteria is in which column?

The criteria is in column 11

 

[goncalogera]

Hi there AiresCamoes and welcome to the community. You can try this, hope it works!

Sub find_criteria()

Dim sh As Worksheet
Dim sh2 As Worksheet
Dim list As ListObject
Dim n As Long

Set sh = ThisWorkbook.Sheets("Folha3")
Set sh2 = ThisWorkbook.Sheets("Folha2")
Set list = sh.ListObjects("Tabela1_2")

n = sh2.Range("A" & Application.Rows.Count).End(xlUp).Row

list.Range.AutoFilter field:=11, Criteria1:=Array("16311100-9", _
                                                "92620000-3", _
                                                "77320000-9", _
                                                "18412000-0", _
                                                "18412200-2", _
                                                "18820000-3", _
                                                "18932000-1", _
                                                "16150000-1", _
                                                "37400000-2", _
                                                "37410000-5", _
                                                "37412000-9", _
                                                "37452000-1", _
                                                "37450000-7", _
                                                "37451000-4", _
                                                "37453000-8", _
                                                "45112720-8", _
                                                "45212221-1", _
                                                "45212223-5", _
                                                "45212225-9", _
                                                "45242100-6", _
                                                "77320000-9"), _
                                                Operator:=xlFilterValues

list.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

sh2.Activate

sh2.Range("A" & Application.Rows.Count).End(xlUp).Select

ActiveCell.Offset(1, 0).Select

ActiveCell.PasteSpecial

End Sub

 

[AiresCamoes]

Hi there AiresCamoes and welcome to the community. You can try this, hope it works!

Sub find_criteria()

Dim sh As Worksheet
Dim sh2 As Worksheet
Dim list As ListObject
Dim n As Long

Set sh = ThisWorkbook.Sheets("Folha3")
Set sh2 = ThisWorkbook.Sheets("Folha2")
Set list = sh.ListObjects("Tabela1_2")

n = sh2.Range("A" & Application.Rows.Count).End(xlUp).Row

list.Range.AutoFilter field:=11, Criteria1:=Array("16311100-9", _
                                                "92620000-3", _
                                                "77320000-9", _
                                                "18412000-0", _
                                                "18412200-2", _
                                                "18820000-3", _
                                                "18932000-1", _
                                                "16150000-1", _
                                                "37400000-2", _
                                                "37410000-5", _
                                                "37412000-9", _
                                                "37452000-1", _
                                                "37450000-7", _
                                                "37451000-4", _
                                                "37453000-8", _
                                                "45112720-8", _
                                                "45212221-1", _
                                                "45212223-5", _
                                                "45212225-9", _
                                                "45242100-6", _
                                                "77320000-9"), _
                                                Operator:=xlFilterValues

list.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

sh2.Activate

sh2.Range("A" & Application.Rows.Count).End(xlUp).Select

ActiveCell.Offset(1, 0).Select

ActiveCell.PasteSpecial

End Sub

Thank you it worked perfectly!