Copying and pasting without replacing conditional formatting

[rjsnieg]

Here is a quick workaround that I have used successfully to get around Excel's conditional formatting reference issues when copying and pasting.

In my case I only need the values and comments. I copy the entire active cell, then "dump" the values and comments to an area well outside of my data. Once that is done, clear the origin cell contents and comments. Then we need to copy the "dumped" cell again.

The paste macro pastes the values and comments to the active cell then clears the "dump area".

Once the following code is in a module, assign keyboard shortcuts to the copy and paste macros and use these instead of the native copy and paste shortcuts.

Hope this helps somebody!

Sub copy()

Application.ScreenUpdating = False
ActiveCell.copy
' dump the values and comments before clearing the original cell (does not copy origin cell formatting)
ActiveCell.Offset(800, 0).PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteComments)
' clears the contents and comments of the origin cell (does not clear origin cell formatting)
ActiveCell.Offset(-800, 0).ClearComments
ActiveCell.Offset(-800, 0).ClearContents
' copys the new cell to the clipboard
ActiveCell.copy
Application.ScreenUpdating = True



End Sub

Sub paste()

ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteComments)
'clears the range where the copy "dumped" the cell data
Range("D800:GC1200").Clear

End Sub