Excel 2010, Looking for VBA to copy Cells with Conditional Formatting to another sheet and paste the contents including the CF Formats

Conditional Formatting is based on the rule(s) that user defined. Without copying the rule(s) means there is no Conditional Formatting copied and applied to the targeted range.

or is that mean you want to copy and paste the values, with format?

Thanks for posting, Ryan.

No, I need to copy the values with all Formats, including the CF, but as a static format because the Conditions won't exist in the New Sheet and the Rules wouldn't work.

I actually don't quite understand your requirement.

The sample attached is enabled user to copy and paste with CF and cell formatting.

it just seems working for me.

or you can tell us what's your requirement based on this sample attached?
28698278.xlsm

@Ryan

Your code is simply copying everything from the source range to the destination range.
I think that he wants to copy everything but the conditional formatting rules and wants to retain the colors applied by the the conditional formatting in the source range.

>>I think that he wants to copy everything but the conditional formatting rules and wants to retain the colors applied by the the conditional formatting in the source range

Then I think it needs to do a quite complex verification on conditional formatting's rules in which we need to determine the rules' sequences and formulas that matched the conditions (cell's data), and then grab the associated color accordingly, and then finally applied to the targeted cells.

Not yet writing a test script for this, but is this worth to do this? Reason being... If we managed to write the script above, the result is as same as when we applied the conditional formatting. From a user prospective of view, it's the same. Hence, for this reason, it not persuade me to write the similar script like this.

Thanks for joining the discussion, sktneer.  And yes, your interpretation is exactly what I need.

@Ryan:  

... but is this worth to do this? Reason being... If we managed to write the script above, the result is as same as when we applied the conditional formatting. From a user prospective of view, it's the same.

Let me clarify:  In one scenario, I have 9 separate CF Rules that apply to one Row in the Source ws, and I want to copy that Row as it appears to a new ws.  The Rules include references to Conditions that only exist (in various places) on the Source ws, so copying the Source Row to a new sheet would not work because those References don't exist in the destination (new) ws.

In another scenario, I have a range of AutoFiltered Rows that have 2 CF Rules that apply to them.

Hope this is clear.

I've found a page that might help solve this, but it seems to deal with preserving the CF of a given Range in place and then deleting the CF.

Haven't sorted out how to apply this to my situation yet (where I am Pasting the Source Range to another newly created sheet, AND I don't want to Close the Source ws without Saving as that question proposed (because the Source ws is in the same wb as the Destination ws), but it sounds promising:

Solved problem myself.  Thanks for having a look at this.