Filter a table using a different name each time and paste the results in a new file

The data:
- Sheet1 contains a source table (its size changes every Qtr), and
- Sheet2 contains an index table, containing 'ProfitCenter Name' and 'Analyst email' headers.

The mission:
(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!:thumb

Number 1 is the bottleneck at the moment https://www.mrexcel.com/forum/images...icon_smile.gif