VBA Copy and Paste Values Into the Next Empty Row in Excel

To copy a range as values into the next empty row in Excel, assign the source range’s .Value to a same-sized destination range beginning at the next row. The macro below appends the values from Entry!A2:D2 to the first available row in Log, using column A to find that row.

A VBA macro to append values

This example assumes both worksheets are in the workbook containing the macro, the destination sheet has column headings in row 1, and column A is filled for every saved record. Change the sheet names, source address, and destination column to match your workbook.

Sub AppendValuesToNextRow()
    Dim wsSource As Worksheet
    Dim wsLog As Worksheet
    Dim sourceRange As Range
    Dim nextRow As Long

    Set wsSource = ThisWorkbook.Worksheets("Entry")
    Set wsLog = ThisWorkbook.Worksheets("Log")
    Set sourceRange = wsSource.Range("A2:D2")

    ' Find the next row using the consistently populated ID column.
    nextRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row + 1

    ' Write the source values, not its formulas or formatting.
    wsLog.Cells(nextRow, "A").Resize(sourceRange.Rows.Count, _
        sourceRange.Columns.Count).Value = sourceRange.Value
End Sub

Before running any macro, save a copy of the workbook. Excel does not let you reverse a macro with Ctrl+Z, so a mistake in the destination range may require restoring that copy. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm).

How the next-row calculation works

The expression wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row starts at the bottom of column A and moves upward to its last nonempty cell. Adding 1 gives the row after it. If A1 contains a heading and the last record is in row 24, the macro writes to row 25. This is a common way to append records without hard-coding a row number.

The final assignment uses Resize to make the destination the same height and width as the source range. A2:D2 is one row by four columns, so it is written into four cells across the selected destination row. If the source range has several rows, the same code appends the whole block and retains its row and column shape.

The macro uses ThisWorkbook, which means the workbook where the macro’s code is stored. That is helpful when other workbooks are open: it avoids accidentally writing to whichever workbook happens to be active. If the macro is stored in PERSONAL.XLSB or an add-in instead, change the workbook references so they point explicitly to the workbook holding Entry and Log.

Set up and run the macro

Make a backup copy, then open the workbook in Excel for Microsoft 365 on Windows.
Press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module in the editor, then paste the macro into the module.
Replace Entry, Log, and A2:D2 with the actual worksheet names and source range. Worksheet names must match, including spaces.
Return to Excel and run the macro using Developer > Macros, select AppendValuesToNextRow, and choose Run. If the Developer tab is not shown, enable it through File > Options > Customize Ribbon.

On a Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; keyboard combinations can vary by keyboard and function-key settings. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop Excel app to run this procedure.

Append multiple rows or use a different range

To append a block instead of one row, change the source address. For example, setting the source range to A2:D6 sends five rows and four columns to the destination, beginning at the next available row. The destination is sized from the source automatically, so there is no need to change the Resize expression. Check that the source block contains exactly the records you want; blank rows in the block are also copied as blank rows.

If the source and destination columns do not start in the same place, change the destination column in the last assignment. For instance, using column C as the destination start writes the first source field to C and the next three fields to D:F. The code does not rearrange fields; if you need a different order, assign columns individually or build a separate mapping rather than copying one rectangular range.

For a source range that changes size, calculate its last source row before setting sourceRange. The right approach depends on how the input is organized: a named Excel Table, a fixed form range, or a reliably populated key column can each be used. A fixed range is easier to audit, while dynamic detection is convenient when users add a variable number of rows.

Choose a reliable column for finding the empty row

The macro looks for the last used cell in column A, not the last used cell anywhere on the worksheet. That distinction matters. If column A has blanks inside the records, or if records are present only in other columns below its last value, the calculated row could overlap existing data. Use a column that is populated for every record, such as a unique ID or required date.

The example assumes row 1 is a header. With a heading in A1 and no records yet, the calculation returns row 2, which is the correct first data row. If the destination has no heading and should start in row 1, this calculation needs adjustment; otherwise it will start at row 2. Similarly, if the destination data begins below a title or introductory notes, set up the sheet so the chosen key column correctly identifies the final record.

Another common approach is to find the last populated row across the whole sheet. That can be useful when records do not have a single required field, but it requires more code and a clear definition of what counts as used. In a structured log, a consistently populated key column is usually simpler and easier to maintain.

Values versus ordinary copy and paste

Assigning .Value transfers the results stored in the source cells. If a source cell contains a formula such as a calculation, the destination receives its current calculated result rather than that formula. It also does not copy the source cell’s fill color, font, borders, number format, comments, or data validation. This is often desirable for an archive or transaction log where the saved record should not change when the input formula later recalculates.

Values do not automatically make every item plain text. A date is generally stored by Excel as a number with a date display format, and a destination cell with General formatting may show a serial number rather than a date. Apply the needed number formats to the destination columns in advance. If leading zeros are important for an identifier, format the destination as Text or store the identifier as text before appending; a numeric value such as 00123 may otherwise be treated as 123.

Using the clipboard with Copy and PasteSpecial is another option, but it involves copy mode and is easier to disrupt with intervening actions. Direct value assignment is generally clearer when the goal is only to append values. If you deliberately need formatting too, copy/paste or separately set the destination formats rather than assuming the value assignment will carry them.

Common problems and fixes

Subscript out of range: Excel cannot find a worksheet name in the workbook referenced by the code. Check the spelling and spaces in Entry and Log, and confirm both sheets are in ThisWorkbook.
Data lands on top of an existing record: The chosen key column may contain gaps or be blank for later records. Choose a column populated on every row, and inspect the destination before running again.
The macro starts too low: Formatting or an old value in the key column may make Excel regard a lower cell as occupied. Remove unintended content from that column, or use a deliberate table-based append strategy.
Only some fields appear: Confirm the source range has the intended number of columns and that the destination starts in the correct column. A source range of A2:D2 has four columns.
The result looks wrong: Check destination number formats, especially for dates, percentages, and IDs with leading zeros. The code writes values but deliberately does not copy formatting.
The macro does not run: Confirm the file is saved as .xlsm and that macros are permitted by the organization’s security settings. Do not enable macros in an untrusted workbook merely to test them.

When debugging, use a disposable copy of the workbook and test with a small, known source record. Verify the exact destination row and the resulting values before using the procedure on important data.

When a table or formula is a better fit

If users enter many records over time, converting the destination range to an Excel Table with Insert > Table can make the log easier to filter, format, and reference. A table also expands as rows are added, but it does not by itself decide when or how to transfer an input form into a permanent record. VBA can still append to a table, though its destination logic should be written around that table rather than assuming the worksheet layout never changes.

For a one-time transfer, selecting a source range and using Paste Special > Values may be sufficient. For a repeatable action, a macro is useful because it can consistently choose the destination row and avoid copying formulas. If the workbook is shared online or macros are blocked, consider a non-VBA process such as entering records directly into a table, or use an automation tool approved for the organization. Choose based on repeatability, workbook access, and whether VBA can be run in the intended Excel environment.

Frequently Asked Questions

How do I paste values without formulas in VBA?
Assign the source range’s .Value to a destination range of the same dimensions. The destination receives calculated values, not the formulas used to produce them.

How do I paste into the first empty row after a header?
If row 1 is the heading and column A is populated in every record, find the last nonempty cell in column A and add 1, as the example macro does. With only a header present, the next row is 2.

Can this macro append several rows at once?
Yes. Set the source range to a multirow block, such as A2:D6. The Resize expression uses the source dimensions, so it writes the entire block to the destination beginning at the next row.

Why does Excel paste a date as a number?
The value may have been transferred correctly, but the destination cell may not have a date number format. Format the destination column as a date, or copy the needed number format separately.

Can I undo a VBA append with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a separate copy first and inspect the destination before running it on important 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.