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?

I would just like to recommend that you [use the Transpose Worksheet method rather than abusing clipboard](http://stackoverflow.com/a/13176360/429091). — binki, Jun 30 '15 at 19:15

score 43 · Accepted Answer · answered Jan 13 '12 at 15:18

43

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

answered Jan 13 '12 at 15:18

GSerg

76,472
17
159
346

Thanks, I was not putting the pastspecial command on a new line. – user1130306 Jan 13 '12 at 16:03
I had to paste the transpose to a different set of cells. So, if copied from A1:B5, had to past to A6 – Orbiting Eden Mar 18 '17 at 22:03

score 13 · Answer 2 · answered Dec 12 '19 at 00:02

13

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

answered Dec 12 '19 at 00:02

GollyJer

23,857
16
106
174

2

This is better way to do it. Especially if you are looking for high performance. Thanks for the code! – Owennn Feb 24 '20 at 16:05
3

The most underrated answer in all of stack exchange. – DeerSpotter Jun 18 '20 at 13:28
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. – Steven Pomponio Jul 10 '20 at 00:40
@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. – GollyJer Jul 10 '20 at 15:57
3

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. – Steven Pomponio Jul 10 '20 at 17:16
1

You created a different function called `transposeAndPasteColumn`. – GollyJer Jul 14 '20 at 15:25

score 2 · Answer 3 · answered Aug 20 '20 at 19:11

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

Excel VBA - Range.Copy transpose paste

3 Answers3

Linked

Related