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?
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.
Versions Excel vers. 2007+, Mac since 2011, Excel for Microsoft 365
Source: Stackoverflow.com