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

i've used this code to filter the data by colour and priority but having trouble coding the filtered department data to save to a new file.

Please Login or Register  to view this content.

Hi Gti182

This Code is in the attached and appears to do as you require

Please Login or Register  to view this content.

Hi Gti182

If i add an additional row to the top of the "original" tab how would this affect the code

It depends on how the new Row is populated and/or formated...show me.

Hi Gti182

This is what I was concerned about

In the new row A1-C1 will be a merged cell called "Project information"

Show me the new file and I'll play with it...merged cells cause nothing but frustration...we'll see what we can do.

Hi Gti182

The attached workbook dumps the Mergerd Cells and uses Center Across Selection in it's place.
Let me know of issues.

Please Login or Register  to view this content.

You're welcome...glad I could help. Thanks for the Rep.