To paste values without dragging along unwanted formatting is easily done in Excel by using the good old copy/paste with CTRL+C and CTRL+V, and then choose from the drop-down menu “Paste values” to remove formatting.

values

In a worksheet with a lot of formatting and values to move around, all the clicking to select ‘values’ can be tedious. A way to always paste values can be to override the “CTRL+V” shortcut.

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 I could not find this reference among the available references. To solve this just add a UserForm and the reference will be added automatically. UserForms are added by right-clicking in the project-tree and choose Insert->UserForm.

In ThisWorkbook:

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.

“valuesOnly” is the method to be called.

Leave out the method name parameter in OnKey to reset 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:

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.

Try it out, tweak the code to your needs!

Advertisements