Excel VBA Copy and Paste a Row: Practical Code Guide

To copy a row with Excel VBA, specify the source and destination worksheet rows and use Copy Destination. For example, this copies row 2 on a sheet named Data to row 5 on the same sheet, including its formulas and formatting:

Sub CopyOneRow()
    ' Copy row 2 to row 5 on the Data worksheet.
    Worksheets("Data").Rows(2).Copy _
        Destination:=Worksheets("Data").Rows(5)
End Sub

Change the sheet name and row numbers to suit your workbook. This approach avoids relying on a selected cell or the clipboard, and is usually the clearest starting point.

What a VBA row copy includes

A worksheet row is the entire horizontal row, not just the cells currently containing data. In current Excel for Microsoft 365, that means 16,384 columns, from A through XFD. Copying a whole row can therefore carry across formulas, values, formatting, data validation, and other cell properties across that row. It can also copy blank cells and formatting far beyond the visible table.

That behavior is useful when the source row is a complete template, but it may be excessive when your table occupies only columns A through F. For a table with a known range, copy only that range instead. For example, the following copies A2:F2 to A5:F5 on Data:

Sub CopyTableRange()
    ' Copy only the six columns used by the table.
    Worksheets("Data").Range("A2:F2").Copy _
        Destination:=Worksheets("Data").Range("A5:F5")
End Sub

The destination range’s top-left cell determines where the copied cells begin. The destination must have enough room for the source range. If the source is six columns wide, use a destination range starting at the intended first column; Excel fills the corresponding six cells.

Copy a row to another worksheet

To copy from one sheet to another, qualify both the source and destination. Explicit worksheet references prevent VBA from copying to whichever sheet happens to be active:

Sub CopyRowToAnotherSheet()
    ' Copy row 2 from Data to row 5 on Archive.
    Worksheets("Data").Rows(2).Copy _
        Destination:=Worksheets("Archive").Rows(5)
End Sub

Both sheet names must match the worksheet tab names, including spaces. If a sheet is named Monthly Data, write Worksheets("Monthly Data"). A missing or misspelled sheet name causes a run-time error rather than silently choosing another sheet.

For a workbook other than the one containing the macro, qualify the workbook too, such as Workbooks("Report.xlsx").Worksheets("Data"). That workbook must be open, and its name must be correct. In reusable code, assign workbook and worksheet objects to declared variables so the references are easy to inspect and update.

Copy to the next available row

A common task is appending a copied row below existing records. The next example assumes the table is on Data, has headers in row 1, and column A is filled for every record. It finds the next blank cell in column A and copies row 2 there:

Sub CopyToNextRow()
    Dim ws As Worksheet
    Dim nextRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the first blank row after the last used cell in column A.
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ' Copy the template row to the next record row.
    ws.Rows(2).Copy Destination:=ws.Rows(nextRow)
End Sub

The method using End(xlUp) works upward from the bottom of column A to its last nonblank cell. If A has no records below the header, the result is row 2, which is also the source row in this example. Copying row 2 to itself does not append a new record. To handle an empty table, or to use a different header or template row, add logic that matches your layout.

Choose a column that is reliably populated in each record. If column A sometimes has blanks, it may report a row above the actual end of the table. For data arranged as an Excel Table, consider using the table’s ListRows collection to add a record; that approach is more aligned with structured tables than searching a worksheet column.

Copy values only or preserve formatting

The Copy Destination method copies cell contents and formatting, which is appropriate for duplicating a template. If you need values but not formulas or formatting, assign the source values to the destination directly. With a six-column record in A2:F2 copied to A5:F5, use:

Sub CopyValuesOnly()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Transfer the evaluated cell values, not formulas or formatting.
    ws.Range("A5:F5").Value = ws.Range("A2:F2").Value
End Sub

This assignment does not use the clipboard. Formulas in the source become their current results in the destination; number formats and cell formatting are not transferred. The source and destination ranges should have the same dimensions. If you want formulas but not formats, use a copy operation with PasteSpecial and the xlPasteFormulas option instead.

To copy values using the clipboard-based paste method, copy the source and paste only values:

Sub PasteValuesOnly()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Copy A2:F2, then paste only values into A5:F5.
    ws.Range("A2:F2").Copy
    ws.Range("A5:F5").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Use the direct assignment when you only need values; use PasteSpecial when you need a particular paste type supported by Excel. Setting Application.CutCopyMode to False clears the moving copy outline after the paste.

Copying versus inserting a copied row

Copying into an occupied destination replaces the destination cells. It does not make space or push existing records down. If the goal is to insert a duplicate above an existing row, insert first, then copy into the newly created row. For example, this inserts a worksheet row at position 5 and fills it from row 2:

Sub InsertCopiedRow()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Insert a blank row before the current row 5.
    ws.Rows(5).Insert Shift:=xlDown

    ' Fill the inserted row from row 2.
    ws.Rows(2).Copy Destination:=ws.Rows(5)
End Sub

Insertion shifts existing cells downward and can affect formulas, named ranges, and layouts around the insertion point. If the data is an Excel Table, adding a table row is generally preferable to inserting an entire worksheet row, since a worksheet row may alter unrelated content elsewhere on the sheet.

Run the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the macro into the module, and edit the worksheet names and row references. Return to Excel and run it from Developer > Macros, select the macro, and choose Run. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.

On Mac, Excel supports VBA, but the editor and keyboard shortcuts differ by keyboard and settings. Open the editor from Tools > Macro > Visual Basic Editor, or use the Visual Basic control on the Developer tab where available. Excel for the web can open and edit macro-enabled files, but it cannot create, run, or edit VBA macros; use the desktop Excel app for those actions.

Save a copy of the workbook before running a macro. VBA actions cannot be reversed with Ctrl+Z, and a row copy can overwrite existing data. Macros may also be blocked by Trust Center settings or organizational policy, so only enable or run code from a source you trust. Save code in a macro-enabled workbook such as .xlsm if you need to keep it with the file.

Common errors and how to avoid them

Wrong active sheet: Code using ActiveSheet.Rows(2) depends on which sheet is active. Qualify both rows with the intended worksheet to make the target explicit.
Destination overwritten: A copy to an occupied row replaces its cells. Check the destination or insert a row first if existing records must be preserved.
Unexpectedly large copy: Rows(2) includes every column. Use Range("A2:F2") when the task concerns only a defined table range.
Incorrect next-row detection: A last-row search based on column A is only dependable if A is consistently populated. Select a stable key column or use table-aware code.
Protected worksheet: Protection may prevent the macro from writing to the destination. Check the worksheet’s protection settings and follow the workbook owner’s rules before changing them.
Macro does not run: Confirm that the code is in a standard module, the workbook is saved in a macro-enabled format when necessary, and macros are permitted by the computer’s security policy.

Frequently Asked Questions

How do I copy the current row with VBA? If the active cell is in the row you want, copy it with ActiveCell.EntireRow.Copy Destination:=Worksheets("Data").Rows(5). For a dependable macro, explicitly specify the source worksheet and row instead of relying on the active cell.

How do I copy a row without changing its formatting? Assign a same-sized source range to the destination range with .Value = .Value. This transfers current values, not formulas or formatting. Use a range limited to the columns in your data rather than assigning a whole worksheet row.

Does VBA copy formulas as well as values? Yes. A standard row copy carries formulas, and Excel adjusts relative references according to the destination, as it does when copying cells normally. A values-only assignment transfers the formula’s evaluated result instead.

Can I copy a row to another workbook? Yes, provided both workbooks are open. Qualify the source and destination with their workbook and worksheet names. Check that the destination workbook is writable and that the destination cells will not overwrite information you need.

Can I undo a VBA row copy? Usually not with Ctrl+Z. Running VBA clears Excel’s standard undo history, so save a separate copy before running a macro that changes workbook data.

Choosing the right approach

For a one-off duplicate that should retain formulas and formatting, use Rows(source).Copy Destination:=Rows(target) with explicit worksheet references. For a small table range, copy only its columns. For a values-only transfer, use direct value assignment; for a new record, determine the destination carefully or add a table row. The key decision is whether you mean copy into an existing location, append below the data, or insert and shift existing rows. Those operations have different effects, so make that choice explicit before running the macro.

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.