Excel shortcut for Paste Special Values

Here is a simple solution for a handy shortcut to paste copied Excel data as values. The idea is that you add a simple VBA code to your Personal.xsb file and create a shortcut that you like.

1. Press Alt + F11 and locate your Personal.xlsb file.
Personal.xlsb is workbook which opens hidden every time you open Excel. So macros stored in Personal.xlsb are always available.

If you don’t have Personal.xlsb, then go to View -> Macros -> Record Macros and record some random things in Personal.xlsb.
That way it will be created.

2. Insert this VBA code in Personal.xlsb module and save that (Ctrl + S).

Public Clipboard As New MSForms.DataObject

Sub PasteValues()

  Dim DataObj As New MSForms.DataObject
  Dim S As String
  
  DataObj.GetFromClipboard
  On Error Resume Next
  S = DataObj.GetText
    
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End Sub

3. In the Excel workbook go to View -> Macros -> View Macros and choose macros in PERSONAL.xlsb, then select PasteValues macro.

4. Click the Options button and create a shortcut that you like. For me the best idea was Ctrl + Shift + V. You can do that by holding the Shift button and inserting V.

5. Now try to copy some formulas in your worksheet and enjoy your Paste Special Values shortcut.

Thanks for the help that I found in this Charles H. Pearson blog post.