Ron de Bruin
Excel Automation

Paste and Transpose icon in QAT crash Excel 2013

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Note : This bug is fixed in Excel 2016

When you use some buttons from the Ribbon a lot it is handy to add them to the QAT(Quick Access Toolbar) so it is easy to click on it when you need them without going to another tab or select it from a dropdown first to use it. One of them that a lot of people use is the "Paste and Transpose"option in the Paste menu.

You can add this button like this to the QAT toolbar :

1. Right click on the QAT and you can choose "Customize Quick Access Toolbar"
2. Select All commands in the Choose commands from dropdown
3. Select Paste and Transpose in this list and click on Add to add it to the QAT
4. Click on OK to close the dialog

But there is a big problem in Excel 2013 in some situations when you do things manual or with VBA code.

Example :

1. Select a few cells
2. Press Ctrl C to copy
3. Press Ctrl N to open a new file
4. Excel will crash now

You have the same problem when you do the same with VBA code, I also see other problems (manual and with VBA code) that are fixed by removinging the Paste and Transpose from the QAT so if you have problems remove this icon from the QAT first to test if this fix it.

But what if you really want to have this option in the QAT, read below how you can get it back.

Workeround

We can use a VBA macro below to do the same :

Sub Paste_Transpose()
    CommandBars.ExecuteMso "PasteTranspose"
End Sub

Where do we store this macro and how do we run it ?

The easiest thing that you can do is to copy the macro in your personal file, if you not know what this is or not now how to create it read the information on the page below :

https://jkp-ads.com/rdb/win/personal.htm

How do we run the code ?

We can add a custom button to the QAT to run this macro.

1. Right click on the QAT and you can choose "Customize Quick Access Toolbar"
2. Select Macros in the Choose commands from dropdown
3. Select the macro and press Add to add it to the QAT
4. You can use Modify to select another Icon if you want
5. Press OK to close the dialog

You now have your own Paste and Transpose button in the QAT that is not have the crash problem anymore.

More information

Ribbon and Quick Access Toolbar(QAT) pages
https://jkp-ads.com/rdb/win/section2.htm

Copyright © 2022 Ron de Bruin

Site designed by Spider Web Woman Designs