shortcut/ hotkey Paste Special Transpose

Anyone know how to make a short cut or hot key to do a paste special transpose? Dealing with a lot of data. Want to program it to ctr+f or something of that nature...

I would do a macro that would do this action for you and assign the macro either to a button or a hot key combo you choose yourself. This would be pretty straigh forward with the recorder. If you are having questions I could whip up a sample for you to use after work slows down.

Originally Posted by motown

I would do a macro that would do this action for you and assign the macro either to a button or a hot key combo you choose yourself. This would be pretty straigh forward with the recorder. If you are having questions I could whip up a sample for you to use after work slows down.

That would be greatlu appreciated. I have no clue how to make a macro.

Copy this code:

Please Login or Register  to view this content.

Adding the Macro
(a) Copy the macro above CTRL+C
(b) Open your workbook
(c) Press the keys ALT+F11 to open the Visual Basic Editor
(d) Press the keys ALT+I to activate the Insert menu
(e) Press M to insert a Standard Module
(f) Paste the code by pressing the keys CTRL+V or Edit Paste from the
Menubar
(g) Make any custom changes to the macro at this time (not needed here)
(h) Save the Macro by pressing the keys CTRL+S
(i) Press the keys ALT+Q to exit the Editor, and return to Excel.
(j) Save the workbook.

To run the Macro press ALT+F8 to display the Run Macro Dialog, and select Run. Or simply just just hit CTRL+T and it should work.

You may need to change your macro security under options if excel doesn't allow you to use macros.


Let me know how it goes.

Hey,

Thanks for the help. Sorry it took so long to reply. Work was a zoo. I'm getting an error : "Runtime error '1004':

PasteSpecial Method of Range Class failed"

I'm no good at coding. Any help is appreciated. This would save me a great amountof time

It would be easier to just record your own macro. A macro is simply recording all your actions. Tools - Macro - Record New Macro, at that point you can set your hot key, once you are out of that wizard and start copying, pasting and transposing EVERYTHING is being recorded. Once you're done, click the Stop Macro button (it looks like a filled in square). After that, click your hot key button to repeat the action.

Also, you should read about this in the Help Menu because you can set the macro to go to the exact location you recorded at, or run in relation to your location on the spreadsheet. I know that sounds confusing, but once you practice on a couple small macros and read about it a little, it'll all start to make sense.

My rule of thumb with regard to macros is if I'm doing the same action over and over AND an educated monkey could do the same thing; it's time for a macro.

Good luck

For Excel 2010 you can pin the paste-special-button to the quick access toolbar on top.
If you do this then hitting the alt-button will give it a number as kind of a hot key. Functionality is the same.

motown - thanks for the info. Helped a lot. Had problems using "t" as it creates a table. I instead used "r" and it's great. Good basics for Macros. BTW, the Excel Help article "Copy your macros to a Personal Macro Workbook" helped me understand a little more.