| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 89687 | 2008-05-08 11:31:00 | Excel Macro | GrahamB (750) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 667380 | 2008-05-08 11:31:00 | 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 |
GrahamB (750) | ||
| 667381 | 2008-05-09 01:09:00 | I turned the "Relative Reference" button on the "Stop Recording" floating toolbar & recorded the operation. This is what I got: 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. |
MushHead (10626) | ||
| 667382 | 2008-05-09 01:35:00 | 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. |
MushHead (10626) | ||
| 667383 | 2008-05-09 04:37:00 | Thanks for that. I will try the Reset first, then your Macro. TFYH GrahamB |
GrahamB (750) | ||
| 1 | |||||