Transpose data into Rows

[Rick Rothstein]

You asked this same question in the MrExcel forum where I gave you this macro to do what you want...

Sub RepeatCopyRectangularRange()
  Dim X As Long, Z As Long, RowCount As Long, ColCount As Long
  Dim ColRng As Variant, SourceStartCell As Range, Destination As Range
  Set SourceStartCell = Selection(1)
  RowCount = Selection.Rows.Count
  ColCount = Selection.Columns.Count
  On Error GoTo NoDestination
  Set Destination = Application.InputBox("Select the starting cell on the destination sheet.", Type:=8)
  On Error GoTo 0
  If Not Destination Is Nothing Then
    For X = 1 To ColCount
      ColRng = Cells(SourceStartCell.Row, SourceStartCell.Column + X - 1).Resize(RowCount)
      Destination.Offset(RowCount * (X - 1)).Resize(RowCount) = ColRng
    Next
  End If
NoDestination:
End Sub