Excel VBA - Range Copy transpose paste

Question

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

User · Answer

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  quot A1 A5 quot        Sheet2 Range  quot A1 quot   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

User · Answer

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

User · Answer

Worksheets  Sheet1   Range  A1 A5   Copy Worksheets  Sheet2   Range  A1   PasteSpecial Transpose  True

[excel] Excel VBA - Range.Copy transpose paste

Examples related to excel

Examples related to transpose

Examples related to vba