none
Create macro shortcut for "paste special" RRS feed

  • Question

  • I got some advice previously about making a shortcut icon for "paste special" in Word and now want to do the same thing for MS Excel.  Can anyone help? 

    Thanks in advance! 

    FP

    Tuesday, April 14, 2015 3:21 PM

Answers

  • Here is a macro you can use

    Sub PasteText()
        On Error Resume Next
        ActiveSheet.PasteSpecial Format:="Text"
        If Err Then
            Err.Clear
            Selection.PasteSpecial Paste:=xlPasteValues
        End If
    End Sub

    Copy it into a module in your personal macro workbook PERSONAL.XLSB. If you don't have a personal macro workbook yet, see How do I create and use a PERSONAL file for my VBA code.

    You can assign the macro to a custom button on the Quick Access Toolbar or ribbon the same way as in Word.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 14, 2015 7:02 PM

All replies

  • Here is a macro you can use

    Sub PasteText()
        On Error Resume Next
        ActiveSheet.PasteSpecial Format:="Text"
        If Err Then
            Err.Clear
            Selection.PasteSpecial Paste:=xlPasteValues
        End If
    End Sub

    Copy it into a module in your personal macro workbook PERSONAL.XLSB. If you don't have a personal macro workbook yet, see How do I create and use a PERSONAL file for my VBA code.

    You can assign the macro to a custom button on the Quick Access Toolbar or ribbon the same way as in Word.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 14, 2015 7:02 PM
  • Hi,

    I've moved this thread to Excel for Developers forum since it's related to macro, here you can get more dedicated support.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Melon Chen
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs. Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, April 15, 2015 6:50 AM
  • If you only want to show the Paste Special dialog simply add the icon to the QAT. In 2007 (not later versions) you can also add some of the Paste Special options, eg paste values, though not paste text. Easiest of all though is the keyboard shortcut Ctrl-Alt-V

    For specific paste special functions not available on the Ribbon would mean using a macro as Hans suggested, but note the Undo stack will be cleared.

    Wednesday, April 15, 2015 10:08 AM
    Moderator
  • Thanks Hans, works a treat. 

    Much appreciated as always, saves me time and frustration! 

    FP 

    Friday, April 17, 2015 9:22 AM