VBA to Paste Values only without changing the formatting

[H_gupta]

Hi ,

I want to restrict the worksheet to Paste Values only without changing the formatting of the cells. This should be applicable for all methods of pasting, Ctrl+v, or paste using right click, drag and drop

Using the following code, which works fine in all cases, except when i put a value in the cell and drag it gives a Error : code has been interrupted" - marked below
Any solution ?


' this code will undo PASTE and instead do a PASTE SPECIAL VALUES which will
' allow you to retain FORMATS in all of the cells in all of the sheets, but will
' also allow the user to COPY and PASTE data
Dim UndoString As String
Dim srce As Range
On Error GoTo err_handler
UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)

If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then

Exit Sub

End If

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Undo


If UndoString = "Auto Fill" Then

Set srce = Selection

srce.Copy

Target.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False

Application.SendKeys "{ESC}"
Union(Target, srce).Select

Else

Target.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False

End If

Application.ScreenUpdating = True
Application.EnableEvents = True **Code has been interrupted Error**

Exit Sub
err_handler:
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[Zot]

Where the code is located? In worksheet module?
Why you want to drag it after run VBA?

 

[H_gupta]

Where the code is located? In worksheet module?
Why you want to drag it after run VBA?

Hi,

This is in the worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)

The sheet is used by users to enter data, many times they copy values using dragging feature in the column.

 

[Zot]

That is the problem I guess. Maybe just disable it drag drop ?

Application.CellDragAndDrop = False