Transpose macro help

Microsoft Excel 2003 (full product)
February 3, 2010 at 15:52:39
Specs: Windows XP, 2000
How can I make the following macro act on 14 vertical cells beneath and including whatever cell I activate it from, rather from specific named cells? And then paste/transpose to the 14 adjacent cells to the right in the same row its activated from?

Right now it takes the 14 cells in a column T167 to T180, copies and transposes them to row 167 columns U to AH. Then it deletes the column data originally selected (except for the 1st cell in the column of data).

Thanks very much, Sean.

Sub row14()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub

See More: Transpose macro help

Report •

February 3, 2010 at 16:32:19
Try this:

Sub row14v1_1()
'Copy the 14 Row range starting with the ActiveCell
  Range(ActiveCell, ActiveCell.Offset(13, 0)).Copy
'Paste/Transpose the data into the next Column
  ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
'Delete the range but leave the original ActiveCell
'Note: The current ActiveCell is the first cell of the Transposed range
'so we must compensate for that
  Range(ActiveCell.Offset(1, -1), ActiveCell.Offset(13, -1)).Delete Shift:=xlUp
End Sub

Report •

February 3, 2010 at 16:54:15
Great, thanks very much.

The logic makes sense.

At first it just deleted the contents of the active cell. I made a copy of the macro and it worked.

With the working version the macro pauses with the message that simply gives the address of the target cell where the row is pasted. Not sure why it's doing this.

Thanks again, Sean.

Report •

February 3, 2010 at 17:03:46
re: the macro pauses with the message that simply gives the address of the target cell

Right after I responded, I edited my post and took that line out. You must have copied the code prior to that edit.

I was playing with something else when I was testing the code and forgot to delete that one line.

Either comment it out by putting a single quote in front of it or delete it.

Report •

Related Solutions

February 3, 2010 at 20:33:57
Thanks again, working great, Sean.

Report •

Ask Question