Hi team,

I have large workbook with loads of conditional formatting etc. Sadly, whenever one of my peers copies cells, they never just copy the cell value, but the whole cell incl. formatting. This gets extremely messy over time and I need to constantly clean up as performance of the sheet gets impacted.

I found this handy script, which seems to only allow to paste values across the sheet:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Target.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub
So far so good. Only issue is that if copied content remains in the clipboard, it automatically pastes into any cell I select (without telling Excel to paste). That's obviously not what I intended.
Any idea on how I could tweak this? Already set the mode to false, but that doesn't solve it.

Also, another side question: is there any way to prevent cells to be copied/pasted over cells that contain a dropdown list? Sadly, that also seems to happen regularly. The cells are protected and an error shows if you try to type something, however, pasting content still seems to work (annoyingly).

Any help appreciated. Thanks so much!