Paste Value Transpose Shortcut?

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
I use the Paste Value shortcut button that I've added to my toolbar in Excel. Just as regularly, I also need to do a Paste Special, Values Only with the Transpose option selected. Is there a way to create my own shortcut icon and build code to do this?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Select what you want to copy.
Ctrl-C, (to copy)
Select cell where you want result.
Hold down Alt, then hit E,S,E,
Hit OK.
 
Upvote 0
Thanks ... I was after a way to create a shortcut icon to place on my Toolbar, where having just copied the data .... I could then just click in a cell that I want to paste special, values only and transpose ... then click a shortcut icon to perform that action. Is there a way to create such an icon?
 
Upvote 0
Right mouse over a toolbar and select customize, select the Commands tab, in categories select Macros, click and drag the Custom button onto your toolbar, then right mouse over it and select Assign Macro. (you also have options to change the icon and name).

Here is the code for the icon...
Code:
Sub TransposeCopy()
With ActiveCell
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub
End With
 
Upvote 0
Worked perfectly when I switched the End Sub & End With around at the end. Thanks again.

Sub TransposeCopy()
With ActiveCell
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
End Sub
 
Upvote 0
The problem that I'm having with this is that I created the above in the VBA Editor screen and it is now just workbook specific. How can I adjust this so my icon is on the toolbar for all workbooks?

Unfortunately, it may be the IT department blocking users from having/changing global settings. Is there a way to use this as a macro that I can save to my local settings. Thanks.
 
Upvote 0
If you wish to always have the macro available and not just when one particular workbook is open - i.e. the macro is a "generic" macro; it is better to place the macro in your Personal Macro Workbook. This is a workbook that Excel automatically creates. If you use the Macro Recorder to record a macro, the dialog box asks where you want to store the macro: (a) the active wb, (b) a new wb or (c) in your Personal Macro wb. If you select Personal Macro Workbook and you have not previously recorded a macro there, Excel will create it for you, naming it Personal.xls and placing it in your Excel startup directory. This way every time you start Excel, Personal.xls will load and its macros will be available to you. (Note that Personal.xls is a hidden workbook and not an Excel Add-In.)
Excel will put it in your startup directory. Normally that's going to be:
C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART\
Note that \Application Data\ is a hidden directory and won't show unless you have configured WinExplorer to show hidden folders.
 
Upvote 0
Thanks John, but how do I replicate the VBA that you produced by using the Record Macro function? I thought the Record Macro was for recording specific tasks such as copy/pasting specific cells etc.

This is what I need to save to my Personal Macro Workbook below but how can I get it in there?

Sub TransposeCopy()
With ActiveCell
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
End Sub

Thanks again for your help
 
Upvote 0
John,

Thank you for this lesson on placement of macros. I'm pretty new to VBA and I have been making all of my macros as modules saving as .xla and putting them as Add-In. So their is a way I can just put it in XLSTART and it will aways be there? If you put a macro in XLSTART can it be linked to an icon or shortkey? It's tough being a noob...

Thanks for your help,
Risk
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top