Filter and copy table

[kabootar]

Hi All,

I am struggling with copying over a filtered table from one workbook to another. the workbooks are both open.

The table is in the commission tab in the Sales Master - 2018.xlsm file

So, I need to filter the table to show all the data of the name of the person at the end of the filename, the filename is: "Sales File - Billy.xlsm"
Then copy this filtered table into the commission tab in Billy's sales file

The filepath for the master file is in a named cell in the data tab in Billy's file.

I have tried to code this in different ways but to no avail.

Hope this makes sense.

Thank You!!



Set wkb = ThisWorkbook

Set wkb1 = Sheets("Import Directory (2)").Range("ImportFilePaths_2")



wkb.Sheets("CASH BONUS").Delete



wkb1.Sheet("CASH BONUS").Copy After:=wkb.Sheets("CASH BONUS")



wkb.Sheets("CASH BONUS").ListObjects("table_CashBonus").Range.AutoFilter Field:=3, Criteria1:="<>SelectedName"



ActiveSheet.Range("$B$4:$G$" & Lines).Offset(1, 0).SpecialCells _

(xlCellTypeVisible).EntireRow.Delete

 

[Norie]

What is SelectedName here?

wkb.Sheets("CASH BONUS").ListObjects("table_CashBonus").Range.AutoFilter Field:=3, Criteria1:="<>SelectedName"

Is it a variable with the name you want to filter out?

Is it a named range?

 

[kabootar]

sorry it will be the name of the employee that is part of the filename. So i.e "Billy"

 

[DanteAmor]

Put the following macro in billy's book

Sub Copy_Data()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    '
    Set l1 = ThisWorkbook   'book of billy
    'get name of the person at the end of the filename
    wname = WorksheetFunction.Substitute(l1.Name, ".xlsm", "")
    wname = Split(wname, "-")
    wname = WorksheetFunction.Trim(wname(UBound(wname)))
    Set h1 = Sheets("CASH BONUS")
    h1.Cells.Clear
    '
    'get filepath for the master file from cell in the data tab in Billy's file
    ruta = l1.Sheets("Import Directory (2)").Range("ImportFilePaths_2").Value
    '
    'name of master file
    arch = "Sales Master - 2018.xlsm"
    If Right(ruta, 1) <> "\" Then ruta = ruta & "\"
    'open master file
    Set l2 = Workbooks.Open(ruta & arch)
    Set h2 = l2.Sheets("CASH BONUS")
    h2.ListObjects("table_CashBonus").Range.AutoFilter Field:=3, Criteria1:=wname
    '
    'copy data filtered
    h2.Range("table_CashBonus[#All]").Copy h1.Range("A1")
    '
    'close master file
    l2.Close
    Application.ScreenUpdating = True
    MsgBox "copied data"
End Sub

Test and let me know if you have any questions