Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Paste Value Transpose Shortcut?

This is a discussion on Paste Value Transpose Shortcut? within the Excel Questions forums, part of the Question Forums category; I use the Paste Value shortcut button that I've added to my toolbar in Excel. Just as regularly, I also ...

  1. #1
    Board Regular
    Join Date
    Apr 2006
    Location
    cardiff
    Posts
    176

    Default Paste Value Transpose Shortcut?

    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?

  2. #2
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985

    Default

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

  3. #3
    Board Regular
    Join Date
    Apr 2006
    Location
    cardiff
    Posts
    176

    Default

    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?

  4. #4
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985

    Default

    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
    John

  5. #5
    Board Regular
    Join Date
    Apr 2006
    Location
    cardiff
    Posts
    176

    Default

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

  6. #6
    Board Regular
    Join Date
    Apr 2006
    Location
    cardiff
    Posts
    176

    Default

    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

  7. #7
    Board Regular
    Join Date
    Apr 2006
    Location
    cardiff
    Posts
    176

    Default

    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.

  8. #8
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985

    Default

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

  9. #9
    Board Regular
    Join Date
    Apr 2006
    Location
    cardiff
    Posts
    176

    Default

    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

  10. #10
    Board Regular
    Join Date
    Jul 2006
    Posts
    71

    Default

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com