VBA Copy and Paste to the Last Available Row

To copy data with VBA to the first available row after existing data, find the last occupied row in a reliable column and add 1. For example, use Cells(Rows.Count, "A").End(xlUp).Row + 1 to get the next row in column A, then copy the source range to that location.

What “last available row” means

In Excel, “the last available row” usually means the first empty row below the existing records, not the final row of the worksheet. A worksheet has a fixed maximum row count, but appending data means finding the last row currently in use in the destination list and placing new records immediately after it. If the last record is on row 25, the paste usually starts on row 26.

The choice of column matters. VBA needs a column it can inspect to decide where the existing list ends. If column A contains an ID for every record, it is a sensible reference column. If some records have a blank in A, use a different column that is consistently filled. Otherwise, the code could mistake a gap for the end of the list and paste over existing information.

A basic VBA method

The End(xlUp) method starts at the bottom of a worksheet column and moves upward to the first nonempty cell. Adding 1 returns the row below that cell. This is a common approach for appending data because it does not require a fixed row limit such as 500 or 10,000.

For a quick example, if records are in column A and the next available row should be selected on the active sheet, this statement calculates it:

nextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

This one-line example assumes nextRow has already been declared and the intended worksheet is active. In a reusable macro, qualify both Cells and Rows with the worksheet, so the code does not accidentally use whichever sheet happens to be active.

Complete macro for appending a range

This example copies records from the Input sheet, starting at row 2, into the Archive sheet after its existing records. It assumes column A is populated for every record on both sheets, row 1 contains headings, and the records occupy columns A through D. Change the sheet names, columns, or starting row to match your workbook.

Sub AppendSourceRows()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim sourceLastRow As Long
    Dim destLastRow As Long
    Dim nextDestRow As Long
    Dim sourceRange As Range

    ' Set the worksheets explicitly; do not rely on the active sheet.
    Set wsSource = ThisWorkbook.Worksheets("Input")
    Set wsDest = ThisWorkbook.Worksheets("Archive")

    ' Find the last source record using the column that is always filled.
    sourceLastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    If sourceLastRow < 2 Then Exit Sub ' There are no source records.

    ' Find where the destination records end, then choose the next row.
    destLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
    If destLastRow < 2 Then
        nextDestRow = 2 ' Preserve the headings in row 1.
    Else
        nextDestRow = destLastRow + 1
    End If

    ' Copy all source records in columns A:D to the destination.
    Set sourceRange = wsSource.Range("A2:D" & sourceLastRow)
    sourceRange.Copy Destination:=wsDest.Cells(nextDestRow, "A")
End Sub

To run it in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Run it with the cursor inside the procedure by pressing F5, or return to Excel and run it from Developer > Macros. If the Developer tab is missing, enable it in File > Options > Customize Ribbon. On Mac, open the editor using Tools > Macro > Visual Basic Editor; keyboard shortcuts can vary with keyboard and function-key settings.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain VBA code. Save a separate copy before running a macro: VBA changes cannot be reversed with Ctrl+Z. On Mac, use Command+Z for ordinary Excel undo, but that does not undo VBA actions either.

How to choose the column that determines the last row

The macro does not find an abstract “last row” shared by every column. It finds the last nonempty cell in the reference column. That is why the reference column should be a dependable indicator that a record exists. Common choices include a record ID, date, or required name field. Avoid a column where blanks are normal.

Every record has an ID: use the ID column as the anchor, even if the data to copy spans several other columns.
Some fields can be blank: choose a field that must be filled, or define a more deliberate rule for what counts as a record.
Records have gaps: End(xlUp) finds the last nonblank cell, not the first empty gap between records. It appends below the bottommost entry.
Destination data starts below a heading: explicitly set the first permitted data row, as the example does with row 2.

If the destination is completely empty, End(xlUp) in a column reaches row 1. The example handles that case by setting the first destination row to 2, preserving a heading in row 1. For a sheet without headings, change the empty-destination logic so the first record goes to row 1 instead.

Copying formulas, values, and formatting

sourceRange.Copy Destination:=... is a direct copy. It carries cell contents and formatting, including formulas, number formats, and other copied cell properties. Relative references in formulas can adjust for the new location, as they do with an ordinary worksheet copy. This is useful when the destination should look like the source, but it may also bring formatting you do not want.

To append calculated results as values only, assign the source range’s values to a destination range of the same dimensions. This avoids the clipboard and does not transfer formatting or formulas:

Sub AppendValuesOnly()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim sourceLastRow As Long
    Dim destLastRow As Long
    Dim nextDestRow As Long
    Dim sourceRange As Range
    Dim destRange As Range

    ' Use the same assumptions as the full example: headings in row 1.
    Set wsSource = ThisWorkbook.Worksheets("Input")
    Set wsDest = ThisWorkbook.Worksheets("Archive")

    sourceLastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    If sourceLastRow < 2 Then Exit Sub

    destLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
    If destLastRow < 2 Then
        nextDestRow = 2
    Else
        nextDestRow = destLastRow + 1
    End If

    Set sourceRange = wsSource.Range("A2:D" & sourceLastRow)
    Set destRange = wsDest.Cells(nextDestRow, "A").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Transfer calculated values without formulas or source formatting.
    destRange.Value = sourceRange.Value
End Sub

Choose the version based on the result you need. Use direct copy when formulas and presentation should come across. Use a value assignment when the destination should store the current results without linking back to the source calculations. If you need values plus number formats, that requires an additional formatting step; a values-only assignment will not preserve them.

Appending rows when the source range varies

The full example determines the source’s last row from column A, then copies A:D for every row from 2 through that last row. This works when column A is filled for all source records and every record uses the same four-column layout. If the source has extra columns, extend both the source range and the destination width consistently.

For a fixed source range, such as A2:D10, the destination-row calculation can stay the same; the source range simply does not need to be built from a last-row variable. For a single row, use that row as the source and paste at the computed next row. For multiple separate ranges, take care: the simple range-to-range examples expect one rectangular block, not a collection of nonadjacent cells.

When copying whole rows, consider whether the source and destination have matching layouts. A whole-row copy can transfer far more cells and formatting than a record needs. Copying only the used data columns is generally more predictable and less likely to overwrite unrelated destination content.

Excel Tables and other ways to append data

If the destination is an Excel Table, appending through its data body can be more resilient than treating it as an ordinary range. Tables expand when data is entered directly in the row below them, and calculated-column formulas and table formatting may extend automatically. The exact VBA approach depends on the table’s name and whether it currently has data rows; check those conditions before referencing a table’s data body. The range macro above is simpler for a plain worksheet list, but it does not specifically manage table features.

For a one-time operation, you may not need VBA at all: copy the source records, select the first blank destination row, and paste. For a repeatable import, a macro is useful because it consistently finds the append position. Power Query is another option when the task is to combine or refresh data from sources, rather than perform a one-off copy that changes the worksheet directly.

Common problems and how to avoid them

Data is overwritten: the anchor column may contain blanks near the bottom. Select a consistently populated column and confirm the destination layout before running the macro.
The macro starts at the wrong row: check whether the sheet has headings and adjust the first-data-row rule. A headerless sheet needs different empty-sheet handling.
It copies too many or too few records: the source last row is determined by its reference column. Check that this column has no stray entries below the real list and is filled for each record.
Formulas or formatting are unexpected: direct copying retains more than displayed values. Use the values-only pattern if the destination should contain static results.
The wrong sheet changes: use explicit worksheet variables, as in the examples, rather than unqualified references that depend on the active sheet.
The macro does not run: confirm the file is saved as .xlsm and that macros are permitted by the workbook’s security settings. Only enable macros in files you trust.

A further risk is running an append macro twice: it will normally add the same source records again, because it does not check for duplicates. If repeated imports are possible, add a duplicate-check rule based on a unique ID, or clear and rebuild the destination using a deliberate process. Do not assume that “copy to next row” also means “copy only new records.”

Frequently Asked Questions

How do I paste into the first blank row in Excel VBA?
Find the last nonempty cell in a reliable column with Cells(Rows.Count, "A").End(xlUp).Row, then add 1. Qualify Cells and Rows with the destination worksheet in a full macro.

Does End(xlUp) find the first blank row?
No. It finds the bottommost nonempty cell in the chosen column when starting from the bottom. Adding 1 gives the row after that cell; it does not fill an empty gap higher in the list.

How can I paste without overwriting the header?
Set a minimum first data row, commonly row 2 when row 1 contains headings. If the destination has no records yet, start at row 2; otherwise append after its last record.

How do I copy only values with VBA?
Set a destination range with the same number of rows and columns, then assign destRange.Value = sourceRange.Value. This transfers values, including the current results of formulas, but not formulas or formatting.

Can I undo a VBA paste with Ctrl+Z?
No. Excel clears its normal undo history when a macro makes changes, so Ctrl+Z cannot reverse the macro’s edits. Save a backup copy before running code that changes important data.

Before you run the macro

Check the source and destination sheet names, the record columns, the first data row, and the reference column used to find the last row. Test on a copy of the workbook with a small sample, especially if the destination contains formulas, tables, or data that must not be replaced. Once those assumptions match the workbook, the append pattern is straightforward: locate the final record, move down one row, and copy only the intended range.

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.