VBA to Paste Values only without changing the formatting

H_gupta

New Member
Joined
Mar 26, 2019
Messages
33
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Where the code is located? In worksheet module?
Why you want to drag it after run VBA?
 
Upvote 0
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.
 
Upvote 0
That is the problem I guess. Maybe just disable it drag drop ?

Application.CellDragAndDrop = False
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top