For copy and paste this event code will retain the CF when cells are copied
over.

Note: does not retain the formatting when you Cut and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
On Error GoTo endit
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
endit:
Application.CutCopyMode = False
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Paste the code into that module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Sun, 14 Dec 2008 16:39:00 -0800, MM wrote:

Hi Sheeloo,

Excellent - is there anyway I can set the sheet up so that however data is
coped here, that its "mandatory" to use Edit-Paste Special-Values and not
just "paste" them in?

Thanks