Results 1 to 4 of 4

Thread: Excel Macro

  1. #1
    Member
    Join Date
    Feb 2005
    Location
    Palmerston North
    Posts
    108

    Default Excel Macro

    I need a macro which is capable of taking any cell (say D10), and the 39 below it (D10-D49), Transposing them into the row starting to the right of starting point (E10-AN10), then clearing the original range (D10-D49),and returning the cursor to the start point (D10).

    I can set it up OK but it always works in the same start point. When I first tried, the Macro pop-Up had a relative position Button, but this no longer shows-don't know why.

    Can anyone please help?

    TFYH

    Regards
    GrahamB
    Last edited by GrahamB; 08-05-2008 at 10:34 PM.

  2. #2
    Senior Member MushHead's Avatar
    Join Date
    Jun 2006
    Posts
    506

    Default Re: Excel Macro

    I turned the "Relative Reference" button on the "Stop Recording" floating toolbar & recorded the operation. This is what I got:

    Code:
    Sub Transpose()
    '
    ' Transpose Macro
    ' Macro recorded 9/05/2008 by MushHead
    '
    ' Keyboard Shortcut: Ctrl+t
    '
        ActiveCell.Range("A1:A40").Select
        Selection.Copy
        ActiveCell.Offset(0, 1).Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        ActiveCell.Offset(0, -1).Range("A1:A40").Select
        Application.CutCopyMode = False
        Selection.ClearContents
        ActiveCell.Select
    End Sub
    Seems to rely on the "ActiveCell" object - without the relative references it just used Range("A1:A40), which gave me absolute cell references, just as you described.
    There are 10 types of people in this world - those who understand binary, and those who don't.

  3. #3
    Senior Member MushHead's Avatar
    Join Date
    Jun 2006
    Posts
    506

    Default Re: Excel Macro

    BTW, in case you've accidentally deleted the "Relative Reference" button, you may be able to get it back.

    Go to "Tools/Customize...", select the "Toolbars" tab & highlight the "Stop Recording" entry. Press the "Reset..." button to restore the default toolbar.
    There are 10 types of people in this world - those who understand binary, and those who don't.

  4. #4
    Member
    Join Date
    Feb 2005
    Location
    Palmerston North
    Posts
    108

    Default Re: Excel Macro

    Thanks for that. I will try the Reset first, then your Macro.

    TFYH

    GrahamB

Similar Threads

  1. Macro Excel
    By BoutFam in forum PressF1
    Replies: 4
    Last Post: 12-11-2005, 08:39 AM
  2. Excel Macro
    By in forum PressF1
    Replies: 1
    Last Post: 11-09-2001, 01:03 PM
  3. excel macro
    By in forum PressF1
    Replies: 2
    Last Post: 19-02-2001, 08:32 PM
  4. Excel Macro
    By in forum PressF1
    Replies: 0
    Last Post: 29-10-2000, 10:37 AM
  5. Excel macro
    By in forum PressF1
    Replies: 0
    Last Post: 06-09-2000, 04:53 PM

Posting Permissions

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