Can do...two part operation
First part: record a macro of doing the action paste special, transpose. Couple of details on that. Stay tuned.
Second part: Assign said macro a keyboard shortcut.
First part is involved, but the good news is, you only have to do it once:
- Go to tools, macro, record new macro. Make sure you give it a useful name (like "pasteSpecialTranspose"; can't have spaces in macro names.) The other piece of information you want to make sure to get right is to save the macro in question to the Personal Macro Workbook. That's important.
- Copy some stuff. Doesn't really matter. Let's say it's A1:A5.
- Click on another sheet in the same book. Choose a place to paste; like, A1.
- Right-click, choose "Paste Special," then check the "transpose" box, like you've been doing.
- You should see a floating toolbar with two buttons; the first button is a blue square; if you float your mouse over said blue square, it'll let you stop recording, which is what you want to do right about now.
- Click on tools, macro, macros. You should see one listed called "pasteSpecialTranspose," or whatever you called it. Click "Edit."
- You'll be in the VBA editor (my friend and ally). Your code will probably look like the example below.
Sub pasteSpecialTranspose()
'
' pasteSpecialTranspose Macro
'
'
Range("A1:A5").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub
If it doesn't look EXACTLY like this, don't sweat it. Keep the line that begins with the word "Sub." Keep the line that begins with the words, "Selection.PasteSpecial." Keep the line that says "End Sub." Lose the rest.
Once you have that all squared away, click on the green "X" in the upper-left hand corner that looks like the Excel icon; that'll bring you back to Excel.
Once you're back in there, click on Tools, Macro, Macros. Select your macro, and choose "options." You can then assign it a keyboard shortcut (like CTRL+SHIFT+P, for example.)
HTH.
Cheers
Geoff
