Separate data from Sheet1 to other Sheets based on filtering & conditional formating

[dougwilson1]

Hello all,


Would very much appreciate some help with this one...


I want to take cuts of data from my main sheet (Sheet1) and paste it onto sheet 2,3,4 etc based on some logic and conditional formatting (it has to be conditional formatting, there isn't a workaround to this part unfortunately).


Probably best if i outline what i would like the macro to do step by step:


1. Sheet1 (has all the data): Filter on Cell A7 for any description containing "John"
2. Still on Sheet1, filter column D7 for any cells colored with color code 49407.
3. Copy this filtered data (columns run from A-F if you need this info, the number of returned rows will vary day to day though) and paste on Sheet2 (from cell a14 if possible).


4. Repeat steps 1-3, but this time for any description containing "Louise". Paste to Sheet3. (Guessing you will need to un-filter the data set before reapplying these filters?)
5. Repeat steps 1-3, but this time for any description containing "Ben", "Jack" or "James". Paste to Sheet4.That's it. Is this possible? I'm having trouble working this one out


Thank you & Happy New Year to you all!


Doug

[SamT]

(it has to be conditional formatting, there isn't a workaround to this part unfortunately)

I don't know if that is possible. I have never tried to, but I have seen better programmers say so.

The good news is that you can use VBA Code to achieve the same visual effect as Conditional Formatting, which makes the color format available to all VBA Code.