Excel VBA - Range.Copy transpose paste


I'm trying to something very simple, but I seem to be stuck. 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?

This question is tagged with excel transpose vba

~ Asked on 2012-01-13 15:14:48

The Best Answer is


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

~ Answered on 2012-01-13 15:18:41


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 on 2019-12-12 00:02:01

Most Viewed Questions: