Code is copying data and formats to relevant worksheets, but not copying validation

I am SO hoping someone can help

I work for a NFP, and complete a very large quarterly report. I found this awesome code on contextures which does pretty much exactly what I need called AdvFilterCities, which runs a macro to send rows to individual sheets based on the value in column A.

There are a dozen or so managers all wanting input into the structure of this report, and the fields that are included/excluded are inevitably changed on an almost daily basis :@

My problem is, if on my master sheet (which I am using as the "template") I have all of my data validation set up, when I use the macro, the data validation doesn't get copied to the individual sheets.

Unfortunately the data validation is super important, because the people completing the report struggle with what accumulative totals are, and in one quarter may put a figure of 67, and in the next put 43.


So in total, I have a "Master Sheet" with 24 regions/offices, with their respective programs, of which there are 14 different programs across the regions (some programs are run from multiple offices, such as homelessness assistance). There are then 24 individual sheets - 1 for each regional office.

(I then just Paste, and Paste Special > Paste Link into another sheet from every ind. sheet so that I have a comprehensive report with all data across all sites, that's updated automatically)

If I can have this one master sheet update all the fields on the other sheets, including the data validation, I'd be a happy girl (and less likely to need copious bottles of wine!!)

This is the Code - with all credit to Contextures for their awesomeness!!!:

Please Login or Register  to view this content.

If anyone can help, I swear I will be your biggest ever fan. This is doing my head in.

Try this adaption of the code. I change the Advanced Filter to filter in place and then copy.

Please Login or Register  to view this content.