Results 1 to 7 of 7
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Paste Special Transpose Values (97sr2H)

    Before the Lounge restore someone was looking for something like this.

    Sub PasteTransposedValues()
    If Not Application.CutCopyMode = xlCopy Then
    Beep
    Else
    Selection.PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
    End If
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Special Transpose Values (97sr2H)

    I saw that post this morning, before the crash/restore. If I remember correctly, the problem as originally posted was that CutCopyMode is automatically set to False when you select Tools/Macro/Macros, so by the time you execute a macro like this, there is nothing on the Clipboard.

    The solution for that is to assign the macro to a toolbar button or shortcut key.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Paste Special Transpose Values (97sr2H)

    Ah, yes. Thanks, I missed the point of the question.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Special Transpose Values (97sr2H)

    Hans & John

    I had tried assigning the macro to a button but that did not work, I had not tried assigning it to a kb shortcut, but that does not work either.

    I had tried an opening line CutCopyMode = True, much like John's opening line, but that did not work from the Tools/Macro menu, and nor does John's macro. However assigning either of these macros to a button works fine. Weird.
    Thanks for your help.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Paste Special Transpose Values (97sr2H)

    I can't think why this isn't working for you. Are you assigning the macro using the full path to Personal.xls, like this:
    PERSONAL.XLS!PasteTransposeValues? (See graphic.)

    Is it possible you are trying to operate on a protected WS? You could test for other failure conditions such as in the following untested code fragment:

    If TypeName(Selection) <> "Range" Then
    Beep
    Exit Sub
    ElseIf Worksheets(ActiveSheet.Name).ProtectContents = True Then
    Application.Dialogs(xlDialogProtectDocument).Show
    End If
    Attached Images Attached Images
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Special Transpose Values (97sr2H)

    Yes John, the macro is in Personal.xls just as in your graphic.

    Try copying a few cells on a sheet, you get dancing dashed lines round the selection; click on the Tools menu -dancing lines remain; click on Macro - dancing lines remain; click on Macros - dancing lines disappear, and you have cleared the clipboard before you have even selected which macro to run. If this can be changed it is probably somewhere in the settings, but I have not been able to find it. Anyway assigning the macro to a button circumvents the problem, so it's not worth worrying about.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Paste Special Transpose Values (97sr2H)

    Sorry Michael, I must have misunderstood you. I thought that you weren't able to attach it to a button or a menu, hence my previous post. You certainly are correct that it can't be called via Alt-F8 or Tools, Macro, Run, etc. I've been running it from a button for a couple of years.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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