Paste Value Transpose Shortcut?

[Artorius]

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?

 

[Datsmart]

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.

 

[Artorius]

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?

 

[Datsmart]

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...

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

 

[Artorius]

Excellent. Thank you very much ... a time saver

 

[Artorius]

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

 

[Artorius]

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.

 

[Datsmart]

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.

 

[Artorius]

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

 

[Risk]

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