Copy select columns from one named table to another based on criteria

[Hawsie]

I have a table with a large amount of data. I want to create a summarized version of that table by copying info to another table while only using certain columns from the original table and by filtering the results based on the sales rep name.

I have attached a sample file. I'm fine if the solution has to include VBA.

 

[Luke M]

See attached. I used an event macro to run an AdvFilter on your data.

 

[Chihiro]

Version using PowerQuery with small code to refresh query.

M used in Advanced Editor (for PowerQuery)

let
    Rep = Excel.CurrentWorkbook(){[Name="RepName"]}[Content]{0}[Column1],
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record", Int64.Type}, {"SalesRep", type text}, {"Customer", type text}, {"DateOpened", type datetime}, {"ReceivedOn", type datetime}, {"EndItem", Int64.Type}, {"ProductName", type text}, {"SerialNumber", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Record", "DateOpened", "ReceivedOn", "EndItem", "SerialNumber"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([SalesRep] = Rep))
in
    #"Filtered Rows"

Code in "Summary" Worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2")) Is Nothing Then
    Worksheets("Summary").ListObjects("Data_2").QueryTable.Refresh BackgroundQuery:=Falsee
End If
End Sub

Edit: Advantage for Advanced filter is that it's readily available in most versions of Excel. While PowerQuery is available to Excel 2016 & 2010. Standalone 2013 or Proplus 2013. One advantage for PowerQuery is that it will pull the data into named table without further modification.

 

[Hawsie]

See attached. I used an event macro to run an AdvFilter on your data.

Thank you Luke M, that was fantastic! I'm on Excel 2007 so I'll use the Advanced Filter method.