Today is the day I decided I had enough of Excel’s lack of a Paste Special shortcut key.

Pasting Annoyances

There are several ways to Paste Special via the keyboard but all of them involve multiple key press sequences.  This may have been simple enough, but when the source of the copied content is from a source other than Excel the Paste Special dialogue box differs, and the last couple keys in the sequence differ as well.

Fig. 1 The Excel source Paste Special dialogue

Fig. 2  The Microsoft Word source Paste Special dialogue

To create a shortcut key for a function (that is not represented by a menu button) we must first create a macro for that function.

Macro Errors

A simple macro like:

Sub PasteVal()
    Selection.PasteSpecial Paste:=xlValues
End Sub

works fine if you are copying cells from Excel or objects from Word, Outlook, etc.  If you are copying text, from inside an Excel cell or elsewhere, you will get an error:

Fig. 3  Error pasting text from outside Excel into Excel

You may also get this error if you run the macro via the Developer -> Macros dialog box.  So remember to run your macro via the shortcut, which we will create below.

Paste Special Macro

To create a macro that can handle pasting both types of data we can use an error catching routine.  Also, since we now know that running a macro will clear the undo stack, we should include code that will give us some protection from pasting an erroneously.

' Custom data type for undoing
    Type SaveRange
        Val As Variant
        Addr As String
    End Type
    
' Stores info about current selection
    Public OldWorkbook As Workbook
    Public OldSheet As Worksheet
    Public OldSelection() As SaveRange
'----------------------------------------------------------
Sub PasteValues()

' Set shortcut to Cntl+Shift+V, for example
' Works for Outlook and Chrome AND Excel

' Abort if a range isn't selected
    If TypeName(Selection) <> "Range" Then Exit Sub

' The next block of statements
' save the current values for undoing
    ReDim OldSelection(Selection.Count)
    Set OldWorkbook = ActiveWorkbook
    Set OldSheet = ActiveSheet
    i = 0
    For Each cell In Selection
        i = i + 1
        OldSelection(i).Addr = cell.Address
        OldSelection(i).Val = cell.Formula
    Next cell

' Start paste function
    On Error GoTo ValuesFail
    ' Works for Excel and Outlook, but not Chrome
    Selection.PasteSpecial Paste:=xlValues
    ' Specify the Undo Sub
    Application.OnUndo "Undo the macro", "UndoMacro"
    Exit Sub
ValuesFail:
    On Error GoTo TextFail
    ' Works for Outlook and Chrome, but not Excel
    ActiveSheet.PasteSpecial Format:="Text"
    ' Specify the Undo Sub
    Application.OnUndo "Undo the macro", "UndoMacro"
    Exit Sub
TextFail:
    On Error GoTo PasteFail
    ActiveSheet.Paste
    ' Specify the Undo Sub
    Application.OnUndo "Undo the macro", "UndoMacro"
    Exit Sub
PasteFail:
    MsgBox "Complete Failure"
End Sub
'----------------------------------------------------------
Sub UndoMacro()
' Reinstates data in the selected range

' Tell user if a problem occurs
    On Error GoTo Problem

    Application.ScreenUpdating = False

' Make sure the correct workbook and sheet are active
    OldWorkbook.Activate
    OldSheet.Activate

' Restore the saved information
    For i = 1 To UBound(OldSelection)
        Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
    Next i
    Exit Sub

' Error handler
Problem:
    MsgBox "Can't undo macro"
End Sub
'----------------------------------------------------------
Sub RevertFile()
' From http://www.excelforum.com/showthread.php?t=491103

    wkname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    ActiveWorkbook.Close Savechanges:=False
    Workbooks.Open Filename:=wkname

End Sub

The code above will allow you not only to paste both text and objects into Excel, it will also allow you to undo changes to data in the range selected for pasting.  Even if you erroneously paste over needed data, you can run the RevertFile macro to reload your worksheet to its last saved state.

In case you would like to experiment more with what code works with what sources you can use the code below.

Sub PasteOutlook()

' Set shortcut to Cntl+Shift+B, for example
' Works for Excel and Outlook, but not Chrome

    Selection.PasteSpecial Paste:=xlValues
End Sub
'----------------------------------------------------------
Sub PasteExcelOnly()

' Set shortcut to Cntl+Shift+E, for example
' Works for Excel, but not Outlook or Chrome

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
'----------------------------------------------------------
Sub PasteChrome()

' Set shortcut to Cntl+Shift+C, for example
' Works for Outlook and Chrome, but not Excel

    ActiveSheet.PasteSpecial Format:="Text"
End Sub

One more thing:  save these macros in PERSONAL.XLSB so that your new Paste Special shortcut is available in all your spreadsheets.

I hope these macros will help you as much as they will me!