Paste ONLY paste the values, and nothing else

[mcomp72]

In my workbook, I am trying to make it so if the user pastes any data from another cell in the workbook (or from another workbook), it will only paste the values from the original cell(s), but nothing else, such as the formatting, borders, cell background color, etc. I found some code in another forum for doing this:
https://stackoverflow.com/questions/34800859/excel-vba-always-paste-values-only

I put the code in the ThisWorkbook module, in the Workbook_SheetChange sub, because I want it to apply to ALL sheets of the workbook.

Here’s the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
    Dim UndoList As String
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
 
    On Error GoTo Whoa
   
    If Application.CommandBars("Standard").Controls("&Undo").Enabled = True Then
 
        '~~> Get the undo List to capture the last action performed by user
        UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
   
        '~~> Check if the last action was not a paste nor an autofill
        If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
        Then GoTo LetsContinue
   
        '~~> Undo the paste that the user did but we are not clearing the
        '~~> clipboard so the copied data is still in memory
        Application.Undo
   
        If UndoList = "Auto Fill" Then Selection.Copy
   
        '~~> Do a pastespecial to preserve formats
        On Error Resume Next
        '~~> Handle text data copied from a website
        Target.Select
        ActiveSheet.PasteSpecial Format:="Text", Link:=False, _
        DisplayAsIcon:=False
   
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        On Error GoTo 0
   
        '~~> Retain selection of the pasted data
        Union(Target, Selection).Select
   
    End If
 
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
 
End Sub

The code works exactly as it should if I try to copy & paste data within the same worksheet. BUT, if I copy a cell on one worksheet, and then paste it onto the cell in a different worksheet (inside the same workbook), it does not paste only the values. It also pastes the number formatting, background color, etc.

How can I make it so no matter what sheet in my workbook I am on, the paste function will only ever paste the value, and nothing else?

 

[JoeMo]

Re: Need help making Paste ONLY paste the values, and nothing else

Does this work for you?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim V As Variant
V = Target.Value
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Undo
    Target.Value = V
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

 

[mcomp72]

Re: Need help making Paste ONLY paste the values, and nothing else

Thanks for your suggestion.

This seems to have an effect on whenever I input data into the sheet that is not relating to doing a paste. For instance, If I type data into cell C30 and then press enter, normally the cell that is then selected is C31. However, with your code in the ThisWorkbook module, after I press enter in cell C30, the cursor highlights cell C31 for a moment, but then goes back up to C30. I don't want it to do that.

Also, with this code, I cannot paste data from a different workbook into my workbook. There may be times when my users need to copy data out of a different workbook into this one, and that is not working now. I can do a copy from another workbook, but when I attempt to paste it into my main workbook, nothing happens except for the "ding" sound that Excel sometimes makes.

 

[JoeMo]

Re: Need help making Paste ONLY paste the values, and nothing else

Thanks for your suggestion.

This seems to have an effect on whenever I input data into the sheet that is not relating to doing a paste. For instance, If I type data into cell C30 and then press enter, normally the cell that is then selected is C31. However, with your code in the ThisWorkbook module, after I press enter in cell C30, the cursor highlights cell C31 for a moment, but then goes back up to C30. I don't want it to do that.

Also, with this code, I cannot paste data from a different workbook into my workbook. There may be times when my users need to copy data out of a different workbook into this one, and that is not working now. I can do a copy from another workbook, but when I attempt to paste it into my main workbook, nothing happens except for the "ding" sound that Excel sometimes makes.

This modification should remove the issue in bold font. As far as copy/paste from another workbook, I can't reproduce the issue you cite. Works fine for me.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim V As Variant
V = Target.Value
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Undo
    Target.Value = V
    ActiveCell.Offset(1, 0).Select
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

 

[mcomp72]

Re: Need help making Paste ONLY paste the values, and nothing else

Thanks. Is there a way to make this code run only if the user is doing a PASTE?

 

[JoeMo]

Re: Need help making Paste ONLY paste the values, and nothing else

Thanks. Is there a way to make this code run only if the user is doing a PASTE?

You are welcome. Replace earlier version with this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim V As Variant
If Application.CutCopyMode = False Then Exit Sub
V = Target.Value
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Undo
    Target.Value = V
    ActiveCell.Offset(1, 0).Select
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

 

[DataBlake]

Re: Need help making Paste ONLY paste the values, and nothing else

is there not a way to inform said user to right click and paste as value instead of ctrl + v?
that would probably be the easiest solution, is to just paste as values.
sometimes you just have to beat it into end users.