excel-vba Ranges and Cells How to Transpose Ranges (Horizontal to Vertical & vice versa)
Example
Sub TransposeRangeValues()
Dim TmpArray() As Variant, FromRange as Range, ToRange as Range
set FromRange = Sheets("Sheet1").Range("a1:a12") 'Worksheets(1).Range("a1:p1")
set ToRange = ThisWorkbook.Sheets("Sheet1").Range("a1") 'ThisWorkbook.Sheets("Sheet1").Range("a1")
TmpArray = Application.Transpose(FromRange.Value)
FromRange.Clear
ToRange.Resize(FromRange.Columns.Count,FromRange.Rows.Count).Value2 = TmpArray
End Sub
Note: Copy/PasteSpecial also has a Paste Transpose option which updates the transposed cells' formulas as well.
