Filter table depending on the given criteria on other workbook

[mark692]

guys is this possible in macro

i have 2 workbook

workbook 1= where i put a table

workbook 2= where i will put a criteria

i want a button on workbook 2 that will filter the table on workbook 1 depending on the criteria that i will put on workbook 2

example.

i have a list of names on a table in workbook 1 (John, Red, Michael, Jenn), if i put "Red" on workbook 2 as criteria and click the button the column of names will only filter and show only the record of "Red".

 

[DanteAmor]

Put the following macro in your book 2.
In your book2, on a sheet called "criteria", in cell "B2" write the name. From row 4 and down will appear the result.


In your book1 you should have a table called "Table1", and it should be on the first sheet of your book. The filter will be performed by the first field of your table.

Sub Filter_table()
'
' Filter table depending on the given criteria on other workbook
'
    Dim xCriteria As String
    Dim l1 As Workbook, l2 As Workbook
    Dim h1 As Worksheet, h2 As Worksheet
    '
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets("criteria")
    h1.Rows("4:" & Rows.Count).ClearContents
    '
    xCriteria = h1.Range("B2").Value
    If xCriteria = "" Then
        MsgBox "Put the name"
        Exit Sub
    End If
    '
    Set l2 = Workbooks("workbook1.xlsx")
    Set h2 = l2.Sheets(1)
    h2.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=xCriteria
    h2.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible).Copy
    h1.Range("A4").PasteSpecial Paste:=xlPasteValues
End Sub