Excel VBA - Range.Copy transpose paste

Question

I am following the help menu for PasteSpecial but I cannot seem to get my code to work without an error.

I want to take Worksheets("Sheet1").Range("A1","A5") and paste transpose to Worksheets("Sheet2").Range("A1","E1").

What is the most simple way to accomplish this?

Sager

I would just like to recommend that you use the Transpose Worksheet method rather than abusing clipboard. – Eddy

Answer 1

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

Destructive

Thanks, I was not putting the pastspecial command on a new line. – Sager

I had to paste the transpose to a different set of cells. So, if copied from A1:B5, had to past to A6 – Leontina

Answer 2

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

Vernievernier

This is better way to do it. Especially if you are looking for high performance. Thanks for the code! – Superable

The most underrated answer in all of stack exchange. – Neuromuscular

When I use this code the pasteTarget limits the amount of cells that get pasted. For example, when copying this code it will only paste the value from Worksheets("Sheet1").Range("A1") to Worksheets("Sheet2").Range("A1"). However when I modify the code to Worksheets("Sheet1").Range("A1:E1") it works properly. The main issue with this arises obviously when the range of cells to copy is dynamic, and if the paste target is too small it won't copy everything, and if it is too large it will place #N/A in the leading cells. – Adrenaline

@StevenPomponio The paste target only needs to be the first cell of your target range. The rowToCopy is the only place your dynamic range needs to apply. – Vernievernier

In order for me to get it to work properly Copy Range("A1:A5") to Range("A1:E1") I needed to modify your first code to the following: pasteTarget.Resize(, rowToCopy.Rows.Count) = Application.WorksheetFunction.Transpose(rowToCopy.Value). With the main change to the Resize argument. – Adrenaline

You created a different function called transposeAndPasteColumn. 😃 – Vernievernier

Answer 3

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.

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

Crocodilian