avoid copying format conditions

[lazarandreiflorin]

Hello,

I have a range ("A1:F20") in which everyday someone copies some text and values. The range has a dark green font and a light green fill with no borders but every time I copy the values or text this changes. I know I can use the paste values option but I am not the only one using it. I know it can be formatted via code but I don't know the code or how to make it overrule the conditional formatting rules that copies along with the text or how to make the code work without any button/trigger. Hope I made myself clear. Thanks

 

[duggie33]

Does that data being copied always come from the same location? If yes, you could record a macro of yourself doing a copy and pastespecial/values. A shortcut could be added when you record the macro or a button could be added to run the macro. The code recorded will be pretty sloppy but it should run fast regardless.

I suggest watching a tutorial video on recording a macro first.

Hope that helps,

Doug

 

[DanteAmor]

If you create a sheet called "Temp" and on the "Temp" sheet, you paste your data.
On your sheet in cell A1 you put the formula =Temp!A1 and so on for each of the cells in your sheet up to F20 =Temp!F20


You can also protect your sheet in this way the formulas will not be modified and they will keep the conditional format.

 

[lazarandreiflorin]

no, it gets copied from different workbooks everyday.

 

[duggie33]

I think DanteAmor's idea of a "temp" sheet to paste the data will work perfect for your situation. It would not matter what formatting is of the data on the "temp" sheet. Your range that has a dark green font and a light green fill with no borders would reference the "temp" sheet and its formatting would not change when new data is pasted on the "temp" sheet.

 

[DanteAmor]

no, it gets copied from different workbooks everyday.

All copies must be made on the Temp sheet. Do the test

 

[lazarandreiflorin]

yes, this worked. thank you

 

[DanteAmor]

You're welcome