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. — binki
– binki, Commented Jun 30, 2015 at 19:15

GSerg · Accepted Answer · 2012-01-13 15:18:41Z

46

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

answered Jan 13, 2012 at 15:18

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1130306 Over a year ago

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

Orbiting Eden Over a year ago

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

GollyJer · Accepted Answer · 2019-12-12 00:02:01Z

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, 2019 at 0:02

GollyJer

27.2k18 gold badges126 silver badges190 bronze badges

6 Comments

Owennn Over a year ago

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

DeerSpotter Over a year ago

The most underrated answer in all of stack exchange.

Steven Pomponio Over a year ago

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 Over a year ago

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.

GollyJer Over a year ago

You created a different function called transposeAndPasteColumn. 😃

|

T.M. · Accepted Answer · 2020-08-20 19:11:59Z

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

Collectives™ on Stack Overflow

Excel VBA - Range.Copy transpose paste

3 Answers 3

2 Comments

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

Comments

Linked

Related