Filter a table using a different name each time and paste the results as values in a new file to be emailed

[AmitM]

Sheet1 contains a source table (its size changes every Qtr) and
sheet2 contains an index table, containing 'ProfitCenter Name' and 'Analyst email' headers.
(1) I am trying to filter the source table in sheet 1 each time according to a specific 'ProfitCenter Name', then (2) copy the results and paste them as values in a new file (1 file for each profit center), (3) and then use the 'Analyst email' corresponding to each to 'ProfitCenter Name' to send the file to...
Really appreciate your help!
Number 1 is the bottleneck at the moment

 

[AmitM]

Appreciate any assistance

 

[ravishankar]

which columns are the centrenames located in each sheet
ravi