Excel VBA Copy Paste Transpose: Working Code and Tips

To copy a range and paste it transposed with Excel VBA, copy the source range and use PasteSpecial with Transpose:=True. For example, this turns a range in B2:D5 into a transposed range beginning at F2: the original four rows and three columns become three rows and four columns.

What transpose means in Excel

Transposing swaps a range’s rows and columns. A value in the first row of a source range becomes a value in the first column of the result; a value in the second row becomes a value in the second column, and so on. It does not simply reverse the order of cells. For example, transposing a two-row, three-column range produces a three-row, two-column range.

This is useful when a report, imported file, or data-entry sheet has information arranged in the wrong direction. You might receive monthly totals across columns but need each month on its own row, or have a list of names down a column that must become column headings. VBA is helpful when this change is repeated, such as during a recurring report refresh. For a one-time change, Excel’s Paste Special command may be quicker.

VBA’s Range.Copy method can copy a range, but its Destination argument does not offer a transpose option. The usual VBA pattern is therefore to copy first and then paste with Range.PasteSpecial, setting its transpose argument to True.

A basic VBA macro to copy and transpose

The following example uses a worksheet named Data. It copies B2:D5 and pastes the transposed result starting at F2 on the same sheet. Change the worksheet name and range addresses to match your workbook. The destination is a starting cell; Excel fills the correctly sized result area from there.

Sub CopyAndTranspose()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' Set the worksheet and the two ranges.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("B2:D5")
    Set destinationCell = ws.Range("F2")

    ' Copy the source and paste it with rows and columns switched.
    sourceRange.Copy
    destinationCell.PasteSpecial Paste:=xlPasteAll, Transpose:=True

    ' Clear the moving-copy outline.
    Application.CutCopyMode = False
End Sub

To add the macro in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Return to Excel, select the macro, and run it from the Macros dialog or from the editor. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA code. Excel for the web can display a workbook containing macros but cannot run VBA; use the desktop application.

Before running a macro, save a separate copy of the workbook. VBA actions cannot be undone with Ctrl+Z, so a wrong range or destination can overwrite data without an easy undo. The example also assumes that the Data sheet exists and that F2 is an appropriate output location.

Size the destination correctly

When a source range has R rows and C columns, its transposed result has C rows and R columns. A source range B2:D5 has four rows and three columns, so its result occupies three rows and four columns. Starting at F2, that output occupies F2:I4. The destination cell in the example is only the upper-left corner; you do not need to preselect the full output range when using Paste Special.

It is still important to check the whole output area before running the macro. Existing values or formulas in the cells Excel needs for the result may be overwritten. A destination that overlaps the source is also risky: choose a separate area or a different worksheet unless you have deliberately designed and tested an in-place transformation.

Count the source rows and columns before choosing an output location.
Make sure the complete transposed output area is clear or contains data you can safely replace.
Use a fixed destination for a stable report layout, or calculate one when the source size changes regularly.

Choose what the paste should include

The example uses xlPasteAll, which is appropriate when the transposed copy should retain the source’s copied content and formatting. Paste Special also supports other paste types. For example, changing the paste argument to xlPasteValues transposes the displayed results as values rather than carrying formulas across. This can be useful when the output is meant to be a static snapshot.

Other choices include xlPasteFormats for formatting and xlPasteValuesAndNumberFormats for values together with number formats. Those options can help when the destination should not inherit every aspect of the source. Choose based on the result you need: values-only output will not update if the original formulas later change, while copying formulas may preserve formulas but alter relative references as Excel pastes them.

Formatting can also make a transposed table look unexpected. Column widths and row heights are properties of whole worksheet columns and rows, not ordinary cell contents. A transposed paste does not turn the source’s column widths into destination row heights. If the result needs a particular layout, adjust widths and heights separately after the paste.

Alternatives for one-time or formula-driven transposes

For a one-off operation, select and copy the source range, choose the destination cell, then use Home > Paste > Paste Special > Transpose. In Excel for Microsoft 365 on Windows, the Paste Special dialog also offers a Transpose checkbox. The ribbon route is often easier than creating a macro when you do not expect to repeat the task.

For a result that should update when the source changes, Excel for Microsoft 365 can use the worksheet formula =TRANSPOSE(B2:D5). Enter it in a clear cell outside the source range. In current dynamic-array Excel, the result spills into the required number of rows and columns. If cells in the spill area are occupied, Excel returns a spill error; clear the obstructing cells or choose another starting point. In older Excel versions, TRANSPOSE may require selecting the full output range and confirming the formula with Ctrl+Shift+Enter rather than Enter alone.

A formula-based result is linked to the source, whereas a VBA paste creates copied content at the time the macro runs. That distinction matters in recurring work. Use a formula when the transposed view should recalculate with changing source data. Use a macro when you need a repeatable paste operation, a snapshot, or additional steps in a larger process.

Common problems and how to fix them

If the macro reports a subscript error on the worksheet line, check that the sheet name is spelled exactly as it appears on the tab and that the sheet belongs to the workbook referenced by ThisWorkbook. ThisWorkbook means the workbook containing the VBA code. If the code is stored in a personal macro workbook or add-in but should act on the currently active workbook, the workbook reference needs to be changed carefully; relying on whichever workbook happens to be active can also cause a macro to affect the wrong file.

If Excel says the paste area is not the same size, confirm that the code uses the single destination cell as shown and that the source is a normal rectangular range. Merged cells can make copy-and-paste operations behave differently from an ordinary grid, so unmerge or restructure them before transposing if possible. Also check that worksheet protection or workbook restrictions are not preventing changes to the destination.

If the macro runs but the output is not what you expected, inspect the paste type. xlPasteAll may carry formulas and formatting that are not appropriate for a static report; xlPasteValues may be better for fixed results. If the output appears to be missing, verify that the selected destination is visible and that a later instruction in another macro has not cleared or replaced it.

Macros may also be blocked by Excel’s security settings or by an organization’s policy. Only enable macros in files from sources you trust. If the workbook came from someone else, inspect the code and confirm what ranges it changes before running it.

Windows, Mac, and workbook safety

The VBA approach works in desktop Excel for Windows and Mac, although keyboard shortcuts and some interface details differ. On Windows, Alt+F11 commonly opens the Visual Basic Editor. On Mac, the shortcut may be Option+F11, or Fn+Option+F11 depending on the keyboard; the Developer tab’s Visual Basic command is another route. Excel for the web does not execute VBA macros, so use desktop Excel to run this code.

Saving as .xlsm preserves the macro; saving as a standard .xlsx file removes VBA code from the workbook. Keep an untouched backup before testing, especially when adapting the macro to clear cells, replace existing reports, or process many sheets. Because Ctrl+Z does not reverse actions performed by VBA, a backup is more dependable than relying on the undo history.

For a recurring process, document the source and destination ranges near the code and test with a small copy of the workbook first. If a report grows over time, a hard-coded range such as B2:D5 will not automatically include new rows. Update the range or add logic that determines its size before copying. Avoid adding complicated automation until the basic source, output shape, and paste type are correct.

Frequently Asked Questions

What is the VBA code to paste transpose? Copy the source range, then use destinationCell.PasteSpecial Paste:=xlPasteAll, Transpose:=True. Set destinationCell to the output’s upper-left cell. Use another paste constant, such as xlPasteValues, if you only want values.

Can I transpose without copying and pasting? Yes. In Microsoft 365, enter =TRANSPOSE(B2:D5) in an empty cell to create a linked, dynamic result. For a fixed VBA output, a Paste Special operation is generally the straightforward choice.

Does VBA transpose overwrite cells? It can. Excel writes the result into the destination area, whose dimensions are the source’s columns by its rows. Check that entire area before running the macro, and save a backup because VBA changes cannot be undone with Ctrl+Z.

Can I transpose formulas as values? Yes. Replace xlPasteAll with xlPasteValues in the Paste Special call. The output will contain the formulas’ current results rather than formulas that recalculate from the original references.

Why can’t I run the macro in Excel for the web? Excel for the web does not run VBA. Open the workbook in the desktop Excel application for Windows or Mac, and make sure the workbook is saved in a macro-enabled format if you want to retain its VBA code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.