VBA, filtering, formatting and saving various formatted versions to individual files

Hi All,

I'm looking to automate a recurring monthly task.

Process:
1) Filter original data (tab 1) by colour Column "D" then priority Column "C". Reds first then Amber then Green with colours to be individually ranked by project priority 1's first then 2's, 3, 4, 5 etc.

I've added a filtered data tab (tab 2) to show how the filtered result should look.

2) I then need to save down 6 individual copies of this data filtered by department Column "B". The filename should be called the department name and should only contain that particular departments information.

To illustrate i've filtered by the "Operations" department in the 3rd tab. I'd then need to save down this tab only to a new file called "Operations.xlsm"

Hope this makes sense.

I can do this manually simply enough but going forward there will be many more entries and more departments making it quite time consuming.

Regards
Neill