Hi Again,

I'm trying to copy-paste select columns of data from sheet1 to sheet2(eg: copying columns A,B,C F,I J from Columns A thru O). and this is the method i follow,

1. Copy the selected columns from Sheet1
2. I select a cell in sheet 2 (where I want data pasted) right click and select the paste special option.
3. In paste special, I ensure the All option is chosen and then click on paste link.

My data is pasted however, I seem to be losing the conditional formatting coloring that I applied in sheet1.

Can anyone out there, please let me know the correct approach.

Thanks,
Nate

Anytime you paste (even PasteSpecial with "All" selected") will overwrite any conditional formatting. Make sure that the "Format" on Paste Special is NOT checked.

Thanks shades, but, In paste special (excel 2000) , It does not let me select multiple options (e.g formulas,validation,formats...), Which I need, since my sheet1 has calulated values,manual values(in some),conditional format and validation applied.

When I just use the paste option, the conditional formatting works as long as sheet1 cell has a manual entry instead of a calculated value/formula.

I want to have calculated values in sheet1 and have conditional format retained in my pasted areas. Is this possible?

Probably will have to be done with code. Can you attach small sample?

Hi Shades,

Looks like my problem is solved. Here is what I did!

I copy say sheet1:cell A1:10 and paste it in sheet2:cell B2(destination)

In destination cell,I select paste special, ensure All is selected and click on paste Link

Again select paste special, select format and ok

This ensures the pasted cell carries the conditional format and also updates automatically if source sheet cell value changes.Im recording Macro to make this easy to work.

Thanks!