Hi,

I may be missing something here, but it seems to me that you could
simply use the PasteSpecial method with Paste set to values, i.e:

Range.PasteSpecial Paste:=xlPasteValues


FARAZ QURESHI wrote:
I was advised to use the following code for allowing the user to paste only
values instead of changing the entire cell format by Ctrl+V or Enter

The code went well, or rather great, but after a couple of date I have found
a problem that it doesn't allow me to "Find & Replace" showing error 1004

Upon Debug line 7, i.e. UNDO line, is highlighted

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