Quandary Regarding Conversion of Conditional Formats to Regular Formats During Copy/Paste

Hello. Attached is an example of what I am working on. You will notice the second and third sheets are organized and sorted identically so as the second sheet highlights in orange fill / blue text those values that have changed since the prior iteration of this file was run.

For our front page reporting that goes out to executives, we have, for example, what you see in the first sheet. It is sorted in a more complex fashion and changes every month, so it isn't eligible for a cell-to-cell worksheet-to-worksheet conditional comparison formatting setup.

What I want is for the second sheet data that DOES have the conditional formatting to transfer to our front page (differently organized) 'WI SHSD' sheet such that the orange fill and the blue text is transferred but not the conditional formatting rule that drove it in the second sheet. Make any sense??

Any help would be so greatly appreciated!
Aimee

All good questions. Let me give you a step by step here. Pardon the length.

1) Once each week I run this report. The first thing I must do is copy all data in the 'Data Refinement for Automation' sheet and paste--> values-only into the 'Prior Scorecard Data' sheet next to it that you see.

2) Then I grab the latest data from a separate raw BI Report that is company-wide and over 21K rows long. I go into that BI report and filter for our group's criteria as follows before pasting->values-only into the 'Data Refinement for Automation' sheet you see.
Criteria 1: Col. AY = Blank or Date Greater than or Equal to 1/1/14
Criteria 2: Col. N = Only values with 'Program - SHSD'

3) Using an IF formula at the extreme right of the 'Prior Scorecard Data' sheet to highlight any mismatches with regard to the A-Z sorted Col. A Project IDs in both the 'Data Refinement for Automation' and 'Prior Scorecard Data' sheets, I ensure that all project ID rows fall in the same place on each sheet to allow the conditional formatting that looks for any data changes to work. Once aligned correctly, the conditional formatting will highlight any values in cells that appear in the more current 'Data Refinement for Automation' worksheet *if* those values differ from the 'Prior Scorecard Data' worksheet's cells.

4) Once this is complete, my job is to transfer the data in the 'Data Refinement for Automation' worksheet, along with any highlighted changes, to the various State SHSD worksheets (i.e. WI SHSD, TN SHSD, etc.) by filtering in Col. F for that state.

5) The manner in which the data is to be organized in the individual State SHSD worksheets is 1st by Company Name A->Z and then Exchange Name A-> Z.

If I were to house the conditional formatting that looks for changes since last time the report was run in the indivudal State SHSD (frontpage) worksheets, I would have to have a prior version of those State SHSD worksheets stored within the file with all rows exactly matched up for change comparison conditional formatting to function. This multiplies the size of the file and number of worksheets in the file greatly.

What are your thoughts?