Pasting values manually are done with CTRL+C and CTRL+V, and then choose from the drop-down menu “Paste values” to remove formatting.

values

If you need this to paste values around a lot, without formatting, it’s possible to make it into a macro and override e.g. “CTRL+V”.

For working with the clipboard we’ll need a DataObject from MSForms. To access this object add a reference to “Microsoft Forms 2.0 Object Library” in VBA-Editor->Tools->References…

Note: In Excel 2010 this reference may be missing. To solve this just add a UserForm and the reference will be added automatically.

In ThisWorkbook, activate and deactivate CTRL+V to call sub ‘valuesOnly’.

Private Sub Workbook_Activate()
    Application.OnKey "^{v}", "valuesOnly"
End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "^{v}"
End Sub

^ means the CTRL key.    “^{v}” means CTRL+V.

Leaving out the call in OnKey resets the shortcut back to normal.

By resetting the shortcut in the Workbook_Deactivate method, the shortcut override will not be active in other opened workbooks.

Put this in a module, customise for functionality.

Option Explicit

Sub valuesOnly()

'If ActiveCell.Locked = True Then Exit Sub 'uncomment this if code is allowed to update protected sheets.

'If copied from within Excel
If Application.CutCopyMode = 1 Then
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Exit sub
End If

'If copied from notepad etc.
Err.Clear
On Error Resume Next 'avoid error if there's no text to paste
Dim str As String
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
str = DataObj.GetText
'str = Replace(str, vbCr, "")
'str = Replace(str, vbLf, "")
str = Replace(str, vbNewLine, "")
str = Replace(str, vbTab, "")
str = Replace(str, Chr(34), "") 'Chr(34) is quotes "
Selection = Trim(str)
DataObj.Clear
On Error GoTo 0

End Sub

Error handling may be left out.
This macro lets you copy from any source and removes unwanted characters and paste text only.

vbNewLine is vbCr (Carriage return) and vbLf (Line feed) together. They all means Enter in different systems.

Advertisements