VBA Copy Paste Row: Excel Code and Common Fixes

To copy an entire row to another row in Excel with VBA, use Rows(sourceRow).Copy Destination:=Rows(targetRow). For example, Worksheets("Data").Rows(5).Copy Destination:=Worksheets("Data").Rows(10) copies row 5 to row 10, including its contents and formatting; it replaces what is already in the destination row.

The key choice is whether you want to overwrite a row, insert a new row, or transfer only certain parts such as values. The examples below show each approach and explain where to put the macro, what the code copies, and how to avoid common mistakes.

What a VBA row copy does

Excel worksheets have 1,048,576 rows, and a whole-row copy acts on every column in the selected row. A standard copy transfers cell contents, formulas, and formatting. It can also carry other cell-level details, such as data validation. If you only need a few columns, copying a smaller range is usually clearer and avoids changing cells elsewhere on the destination row.

When formulas are copied normally, Excel adjusts relative references to suit the new location. For example, a formula that refers to the cell one column to the left will generally continue referring to the cell one column to the left after it is copied. Absolute references, marked with dollar signs, remain fixed. This behavior is the same as copying and pasting with Excel’s interface.

A destination in a copy command is not automatically an instruction to make room. Copying row 5 to row 10 writes the copied row into row 10; it does not push the existing row 10 downward. Use an insert operation when you need to preserve the rows already below the destination.

Set up and run a VBA macro

VBA macros run in the Excel desktop application, not Excel for the web. On Windows, open the workbook, press Alt+F11 to open the Visual Basic Editor, and choose Insert > Module. Paste a macro into the module, then run it from the editor with F5 or return to Excel and select Developer > Macros. If the Developer tab is missing, enable it under File > Options > Customize Ribbon.

Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain the code. On Mac, VBA is also available in desktop Excel, though menu locations and keyboard shortcuts can vary by version and keyboard. Excel for the web can open an .xlsm file but cannot run its VBA macros.

Before running any macro that changes a worksheet, save a separate copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the reliable way to recover the original data if the macro does something unexpected.

Copy a row to another row on the same sheet

This macro copies row 5 to row 10 on a worksheet named Data. Change the worksheet name and row numbers to match your workbook.

Sub CopyRowToAnotherRow()
    Dim ws As Worksheet

    ' Set the worksheet that contains both rows.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Copy row 5 over row 10.
    ws.Rows(5).Copy Destination:=ws.Rows(10)
End Sub

ThisWorkbook means the workbook that contains the macro. If the macro is stored in the workbook being edited, this avoids relying on whichever workbook happens to be active. The worksheet name must match the sheet tab name exactly. If your data is on a sheet named Orders, replace Data with Orders.

The destination row is overwritten as part of the copy. If row 10 contains information you need to retain, do not run this version until you have chosen an insertion or a narrower range approach. For a copy between worksheets, declare both sheets and use the destination row on the second one:

Sub CopyRowToOtherSheet()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Set the source and destination worksheets.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")

    ' Copy source row 5 to target row 10.
    wsSource.Rows(5).Copy Destination:=wsTarget.Rows(10)
End Sub

This code also overwrites the destination row, but on the Archive sheet. If the destination sheet is protected, Excel may block the change. Unprotect it through the appropriate workbook process first; do not add a password to code unless you know the correct password and have permission to edit the sheet.

Insert a copied row without overwriting existing data

To add a copy above the current row 10 and move the existing row 10 and the rows beneath it down, use the insert method. The source is row 5, so inserting at row 10 does not change the source’s position.

Sub InsertCopyOfRow()
    Dim ws As Worksheet

    ' Set the worksheet that contains the source row.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Insert a copy of row 5 at row 10.
    ws.Rows(5).Copy
    ws.Rows(10).Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

The copied row is inserted at row 10, and the prior row 10 moves to row 11. Clearing Application.CutCopyMode ends Excel’s copy outline after the operation. Be careful if the source row is at or below the insertion point: inserting rows changes row numbers beneath that point, so you may need to adjust which row the macro copies.

Inserting a whole row can affect formulas, named ranges, tables, and layouts that refer to the surrounding area. If your data is an Excel table, consider adding a table row and copying the relevant table contents instead of inserting an arbitrary worksheet row. The right method depends on how the sheet is structured.

Copy only selected columns or paste values

For a record that uses columns A through F, a range copy is more targeted than copying all 16,384 columns of the worksheet row. The following example copies cells A5:F5 into A10:F10, including formulas and formatting:

Sub CopyPartOfRow()
    Dim ws As Worksheet

    ' Set the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Copy columns A through F from row 5 to row 10.
    ws.Range("A5:F5").Copy Destination:=ws.Range("A10")
End Sub

To transfer calculated results as values while leaving the destination’s existing formatting in place, assign the values directly. The source and destination ranges must have the same dimensions:

Sub CopyValuesOnly()
    Dim ws As Worksheet

    ' Set the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Put the source results into the matching destination cells.
    ws.Range("A10:F10").Value = ws.Range("A5:F5").Value
End Sub

This copies the current values returned by formulas, not the formulas themselves. It also does not copy formatting. If you need formulas but not formatting, use the copy-and-paste-special method with xlPasteFormulas; for values only, xlPasteValues is another option. Paste Special is useful when you need more control over which parts of cells are transferred, while direct value assignment is simple for a values-only transfer.

Choose between copy, insert, and value assignment

Use the method that matches the result you want, not just the one with the shortest code. These choices cover most row-copy tasks:

Overwrite a destination: use Rows(source).Copy Destination:=Rows(target) when the target row is meant to be replaced.
Make space for a new row: copy the source and insert it at the destination when existing rows must move down.
Copy a subset: use a range such as Range("A5:F5") when only specific columns belong in the new record.
Transfer results only: assign one equally sized range’s .Value to another to avoid copying formulas and formatting.

For repeated jobs, such as copying a row for each matching record, the macro needs additional logic to identify which rows qualify and where each copy should go. The simple examples here deliberately copy one known row; they do not search for a value or decide which row should be copied. Keeping those tasks separate makes it easier to verify the selection before expanding the macro.

Common problems and how to prevent them

A frequent error is a “Subscript out of range” message. In this context, it often means the sheet name in Worksheets("Data") does not exist in the workbook referenced by ThisWorkbook. Check spelling, spaces, and whether the macro is stored in the workbook you intend to change.

Another common surprise is losing destination data. A direct copy replaces cells at the destination; it does not ask for confirmation. Test with a duplicate workbook, confirm the source and destination row numbers, and use the insert version if you need to keep existing rows. A macro can run successfully and still put data in the wrong place if the wrong workbook or sheet is active, which is why explicit worksheet references are safer than unqualified Rows(5) commands.

If Excel reports that it cannot change part of a merged cell, the source or destination may contain merged cells that do not align. Unmerge or redesign the affected area, or copy a range whose merged-cell layout matches. Protected sheets, filtered lists, and tables can also make whole-row actions behave differently from the intended record-level operation. Inspect the workbook structure before automating a larger change.

If the macro does not run, check that macros are permitted by your organization’s security settings and that the file is saved in a macro-enabled format if you need to keep the code. Do not change security settings to allow every macro indiscriminately. Use trusted files and follow your organization’s rules.

Frequently Asked Questions

How do I copy a row in VBA without using the clipboard?
For values only, assign the source range’s .Value to an equally sized destination range’s .Value. That avoids copy mode, but it does not transfer formulas, formatting, or other cell features. Use Copy Destination:= when you need a normal Excel copy.

Does VBA copy and paste a row overwrite the destination?
Yes. A standard Rows(source).Copy Destination:=Rows(target) operation writes the copied row into the destination row. To preserve existing rows below a location, insert a row rather than copying directly over the destination.

How can I copy a row to the next blank row?
First define what counts as a blank row and which column reliably identifies a record. Then find the next available row using that column and copy to it. A sheet may contain formatting far below its visible data, so relying on the worksheet’s apparent last used cell can produce a misleading destination.

Can I run a VBA row-copy macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro; the web version can still display and edit many workbook contents.

Can I undo a VBA copy with Ctrl+Z?
Usually not. Running a VBA macro clears or replaces Excel’s normal undo history, so Ctrl+Z is not a dependable recovery method. Save a separate copy before running a macro that changes data.

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.