Excel VBA - Range.Copy transpose paste

Worksheets("Sheet1").Range("A1:A5").CopyWorksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

Here's an efficient option that doesn't use the clipboard.

Sub transposeAndPasteRow(rowToCopy As Range, pasteTarget As Range)    pasteTarget.Resize(rowToCopy.Columns.Count) = Application.WorksheetFunction.Transpose(rowToCopy.Value)End Sub

Use it like this.

Sub test()    Call transposeAndPasteRow(Worksheets("Sheet1").Range("A1:A5"), Worksheets("Sheet2").Range("A1"))End Sub

WorksheetFunction Transpose()

Instead of copying, pasting via PasteSpecial, and using the Transpose option you can simply type a formula

    =TRANSPOSE(Sheet1!A1:A5)

or if you prefer VBA:

    Dim v    v = WorksheetFunction.Transpose(Sheet1.Range("A1:A5"))    Sheet2.Range("A1").Resize(1, UBound(v)) = v

Note: alternatively you could use late-bound Application.Transpose instead.

MS help reference states that having a current version of Microsoft 365, one can simply input the formula in the top-left-cell of the target range, otherwise the formula must be entered as a legacy array formula via Ctrl+Shift+Enter to confirm it.

VersionsExcel vers. 2007+, Mac since 2011, Excel for Microsoft 365