VBA: Separate data from Sheet1 to other Sheets based on filtering logic and conditional formatting

[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