Copy formula and Filter and Copy data from a table into a new worksheet

Hello All,

I have a workbook with a master worksheet, in the first few roles on the worksheet "C3 : G5", i have some formulas that is calculating base on the data on table in "A9: H26".
What i want to do is copy range "C3 : G5" which contains the formula and the filter and copy the data from the range "A9: H26" , giving each worksheet a specific name based on the filtered value in a specific column.

I have been able to get a code here to solve the filter and copy however i have a problem where i want the data to be pasted into a range "A9: H26". This is just a mock up example of what i'm trying to do, please let me know if this makes sense

Thank you