VBA Macro to Copy and Paste a Row Below the Last Row

To copy the last data row into the row immediately below it, find the last occupied cell in a dependable key column, then use VBA’s Copy method with the next row as the destination. The macro below copies columns A:F on a worksheet named Data; change those settings to match your sheet, and save a copy of the workbook before running it because macros cannot be undone with Ctrl+Z.

A VBA macro for copying the last data row

This example treats row 1 as a header, uses column A to identify the last record, and copies the record’s cells from A:F into the next row. It does not select or activate the worksheet, so it can run without moving the user’s selection around.

Sub CopyLastDataRowBelow()
    'Assumes headers in row 1, records from row 2, and data in columns A:F.
    Const FIRST_DATA_ROW As Long = 2
    Const KEY_COLUMN As String = "A"
    Const FIRST_COLUMN As String = "A"
    Const LAST_COLUMN As String = "F"
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim nextRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    'Stop if the key column has no entries.
    If Application.WorksheetFunction.CountA(ws.Columns(KEY_COLUMN)) = 0 Then
        MsgBox "Column A has no data to copy."
        Exit Sub
    End If

    'Find the last nonempty cell in the key column.
    lastRow = ws.Cells(ws.Rows.Count, KEY_COLUMN).End(xlUp).Row
    If lastRow < FIRST_DATA_ROW Then
        MsgBox "There are no data rows to copy."
        Exit Sub
    End If

    nextRow = lastRow + 1
    If nextRow > ws.Rows.Count Then
        MsgBox "There is no row available below the last row."
        Exit Sub
    End If

    'Copy the selected cells into the same columns on the next row.
    ws.Range(FIRST_COLUMN & lastRow & ":" & LAST_COLUMN & lastRow).Copy _
        Destination:=ws.Range(FIRST_COLUMN & nextRow & ":" & LAST_COLUMN & nextRow)
End Sub

In the Visual Basic Editor, change Data to the exact worksheet tab name, including spaces and punctuation. For example, a tab called Monthly Sales would be written as Worksheets("Monthly Sales"). Change LAST_COLUMN if the record extends beyond F, or change both column constants if the record begins somewhere other than A. The code copies cells only across the stated range, rather than copying all 16,384 columns in an Excel row.

How the macro finds the last row

The key line starts at the bottom of column A and moves upward to the first nonempty cell:

ws.Cells(ws.Rows.Count, KEY_COLUMN).End(xlUp).Row

This is similar to pressing Ctrl+Up from the bottom of that column. It does not mean “find the last row with anything anywhere on the worksheet.” It means “find the last nonempty cell in this particular column.” That distinction is important when a sheet has notes, formulas, or other data in columns that are not part of the main list.

Pick a column that every record must contain, such as an order number, date, or name. If column A is blank for the newest record while column B contains its details, the macro will treat the earlier entry in A as the end of the list. In that case, use a different consistently populated column as KEY_COLUMN. Also note that a formula returning an empty string is still a formula in the cell, so Excel’s upward search can treat that cell as occupied.

The macro checks for an empty key column and for a header-only sheet. Without those checks, a simple version could copy a header or a blank row when there are no records. If your sheet has no header, set FIRST_DATA_ROW to 1 and adjust the sheet structure assumptions accordingly.

What gets copied to the new row

The source is the last detected data row, and the destination is the next row down. For example, if the last record is on row 25, the macro copies A25:F25 to A26:F26. It does not insert a row; it writes into the cells immediately below the source. If those destination cells already contain information, the copy can replace it, so check that the next row is available before running the macro.

Excel copies the cell contents and cell-level formatting in the specified range. Formulas are copied as formulas, and relative references generally adjust as they do when cells are copied in the interface. For instance, a formula in F25 that refers to B25 will normally refer to B26 after being copied to F26. Absolute references, such as $B$25, remain fixed. If you want only values, or only formulas, a copy operation may not be the right choice; use explicit VBA assignments for the exact content you want to transfer.

Because this example copies A:F, information in G or later columns is left alone. That is deliberate: copying an entire worksheet row can carry formats or other cell settings across far more columns than a data list needs. It can also make a workbook’s used range unnecessarily large. Define the smallest range that contains a complete record, and make sure the range covers every field you intend to duplicate.

Choosing the right columns and handling gaps

A last-row macro depends on the shape of the data. In a well-formed list, each record has a value in the key column and rows are added consecutively. If there are blank rows between records, the upward search still finds the last nonempty key cell, so a gap in the middle does not by itself cause the macro to stop early. However, a blank key in the final record does cause trouble, even if other cells on that row are filled.

Before adapting the code, check these details:

Header row: Confirm whether the first record is on row 2 or a different row.
Key column: Choose a column populated on every real record, including the newest one.
Record width: Set the first and last columns to include all fields that should be copied.
Destination: Make sure the row below the detected last row is not reserved for totals, notes, or another section.
Formulas: Decide whether the new row should inherit formulas, values, formatting, or some combination.

If the worksheet contains several separate blocks of data, a single “last row” may not describe the correct block. Anchor the macro to the specific table or key column for the list you mean to extend. A macro that searches the entire sheet can mistake a note or a distant formula for the end of the records.

When the list is an Excel table

If the data is formatted as an Excel table, a table-aware approach is often preferable to finding a worksheet row. Tables have a defined range and can automatically extend when data is entered in the row below them. They also support calculated columns, structured references, filters, and table styles. To check whether a list is a table, click inside it and look for the Table Design tab in the ribbon.

For a table, VBA can refer to a ListObject and add a row with its ListRows.Add method. That is more reliable than assuming the table always begins in row 1 or occupies fixed columns. But adding a table row and copying the previous row are distinct operations: a new table row may inherit table behavior and calculated-column formulas, while a separate copy may be needed if you want to duplicate particular values or formulas. Decide which cells should be carried forward; repeating an old order number or date, for example, may be undesirable even if repeating a formula is useful.

For an ordinary range, the macro in this article is simple and direct. For a table that grows over time, use table references in a tailored macro rather than hard-coding worksheet columns A:F. If the table includes a totals row, table methods also help distinguish data rows from that summary row.

How to add and run the macro safely

In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure into the module, and edit the worksheet and column settings. Return to Excel and press Alt+F8, select CopyLastDataRowBelow, and choose Run. On Mac, the Visual Basic Editor and VBA are available in desktop Excel, although keyboard shortcuts can differ by keyboard and settings; use the Tools > Macro menu to access macros if the Windows shortcut does not apply. Excel for the web cannot create or run VBA macros, so open the workbook in desktop Excel.

Save the workbook in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA code. Excel may show a security prompt when a workbook contains macros; enable macros only for files from sources you trust. Make a separate copy before the first run, especially if the destination row might contain existing content. VBA actions generally cannot be reversed with Ctrl+Z, and this macro does not create its own backup or undo record.

Common errors and practical fixes

“Subscript out of range” usually means the worksheet name in the code does not exactly match a tab in the workbook referenced by ThisWorkbook. Check spelling, spaces, and punctuation. ThisWorkbook means the workbook containing the macro; it is not necessarily the workbook currently active on screen. If the code is stored in a personal macro workbook or another file, the target workbook reference may need to change.

The wrong row was copied often means the chosen key column is not filled in on every record, or the macro is looking at the wrong worksheet. Verify the key column and the value of lastRow before changing the copy range. If you use End(xlUp) on a column containing formulas far below the visible list, those formulas can affect the result.

Only part of the record appears means the configured range is too narrow. Expand LAST_COLUMN to include the rest of the fields, and confirm that the source and destination ranges have matching widths. If formatting appears but formulas do not behave as expected, inspect whether the formulas use relative or absolute references.

The macro overwrote something means the target row was not actually clear. This routine intentionally copies into the next row; it does not insert space or ask before replacing cells. If a total row or note follows the list, change the design so the new record is inserted in the intended location, or add a check that refuses to proceed when the destination range is occupied.

Alternatives to copying the previous row

Copying a row is useful when the next record should inherit formulas, number formats, borders, or data validation. It is not always the right way to append information. If you are importing new records, it may be safer to write only the imported values and let table formulas fill calculated columns. If the row should be blank but retain formatting, copy formats only rather than copying old contents. If the range is an Excel table, adding a table row can preserve the table’s structure more naturally.

You can also perform the operation manually by selecting the last record’s cells, copying them, and pasting into the row below. That is suitable for an occasional task and lets you inspect the destination first. A macro becomes useful when the action is repeated or forms part of a larger process, but automation makes it especially important to define the source, destination, and fields precisely.

Frequently Asked Questions

How do I copy the entire worksheet row instead of columns A:F?

Replace the bounded range copy with ws.Rows(lastRow).Copy Destination:=ws.Rows(nextRow). This copies the full row, not just the used data fields, so use it only when that is intended. It can also copy formatting across unused columns.

Does the macro insert a new row?

No. It copies cells into the row directly below the detected last row. If that row already contains data, the copy can overwrite it. Inserting a worksheet row is a different operation and requires additional VBA, particularly if the list has totals or other content below it.

Can I copy only the values?

Yes. Instead of using Copy, assign the source range’s values to the destination range’s values. For example, with source cells A25:F25 and destination A26:F26, the assignment is ws.Range("A26:F26").Value = ws.Range("A25:F25").Value. In a reusable macro, build those ranges from lastRow and nextRow. This transfers values rather than formulas and formatting.

Why does it copy a row that looks blank?

The row may contain formulas that display an empty string, spaces, or other content Excel treats as occupied. The macro searches the chosen key column, not what looks visually filled across the whole sheet. Inspect that column and choose a key with reliable entries.

Can this macro run in Excel for the web?

No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to use VBA. A cloud-based automation alternative may suit a web-only workflow, but it is a separate technology and does not execute this VBA procedure.

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.