Excel VBA: Copy and Paste a Dynamic Range Automatically

To copy a range that grows or shrinks, use VBA to find its last populated row and column, then copy the resulting range to a destination. The example below assumes headers are in row 1, records start in row 2, and every record has a value in column A.

What makes a range dynamic?

A fixed reference such as A1:D20 always covers the same cells. That works until the source data gains a row or column: the macro may then leave out new information, or copy extra blank cells. A dynamic range is calculated when the macro runs, so its boundaries can reflect the data currently in the worksheet.

There is no single definition of “last cell” that works for every workbook. A common, dependable pattern is to use a column that is populated for every record to find the final row, and the header row to find the final column. For a table with an ID in column A and headers across row 1, these boundaries describe a rectangle from A1 through the last record and last header.

That pattern is different from an Excel dynamic array. Functions such as FILTER can return changing results in worksheet cells, while VBA calculates a range reference and carries out an action such as copying it. This article focuses on copying worksheet cells with a macro.

Copy a changing range with VBA

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the macro below into the module, and change the worksheet names if yours are different. In the example, data is copied from the Data sheet to the Archive sheet, starting at A1.

Sub CopyDynamicRange()
    ' Copy the used data rectangle from Data to Archive.
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim sourceRange As Range

    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsDestination = ThisWorkbook.Worksheets("Archive")

    ' Column A must contain a value for every data record.
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' Row 1 contains the headers across the source data.
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

    If lastRow < 2 Then
        MsgBox "No data records were found below the header row."
        Exit Sub
    End If

    Set sourceRange = wsSource.Range(wsSource.Cells(1, 1), _
                                     wsSource.Cells(lastRow, lastCol))

    ' Copy cells, including formulas and formatting, to the destination.
    sourceRange.Copy Destination:=wsDestination.Range("A1")
    Application.CutCopyMode = False
End Sub

The macro finds the last used row in column A by starting at the bottom of that column and moving up to the first non-empty cell. It finds the last header column by starting at the far right of row 1 and moving left. It then builds one rectangular range and copies it in a single operation. Using the Copy method’s Destination argument avoids selecting cells or relying on the clipboard as an intermediate step.

If the source has headers in row 1 and records in rows 2 through 18, with data through column F, the copied range is A1:F18. If a new record is added in row 19 and column A is filled, the next run includes that row automatically.

Choose reliable boundaries for your data

The sample depends on its layout assumptions. Column A must be filled for each record, and the first row must contain headers extending across the data. If column A contains occasional blanks, the macro can stop at a blank record and miss later rows. Select a different always-populated key column, such as an order number in column B, by changing the column used in the lastRow statement.

If you need to find the final row based on any populated cell rather than one key column, Excel’s Find method can search the sheet. That approach needs deliberate settings: Find remembers some options from previous searches, and formulas that display empty strings can affect what counts as populated. For a well-structured list, a consistently filled ID column is often easier to understand and maintain.

The last-column calculation also makes an assumption: headers are contiguous across row 1. If a blank header separates populated columns, moving left from the end of row 1 may stop at that blank and omit columns to its left or right depending on the layout. Keep headers together, or use a more specific method suited to your sheet.

Data begins in another cell: Adjust the two boundary cells in the Range statement and the destination’s starting cell.
Headers are not in row 1: Use the actual header row when calculating the last column, and set the range’s top row to the correct header or first data row.
Several columns can be blank: Base the final-row calculation on a reliable identifier, rather than assuming every column is complete.
The source is an Excel Table: Consider referring to the table’s DataBodyRange and HeaderRowRange instead of calculating boundaries manually. Tables expand as rows are added.

Decide what should be pasted

The Copy Destination version transfers cell content and presentation, including formulas, number formats, and other formatting. This is appropriate when the destination should resemble the source. It does not mean every workbook feature is a simple value: formulas copied to a new location may adjust their relative references, just as they do during a normal Excel copy.

If the destination should contain only the current results, assign the source values to a same-sized destination range instead. This avoids copying formulas and formatting. The following alternative uses the same boundary logic; replace the sheet names and destination cell as needed.

Sub CopyDynamicValuesOnly()
    ' Copy calculated values without formulas or formatting.
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsDestination = ThisWorkbook.Worksheets("Archive")

    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

    If lastRow < 2 Then
        MsgBox "No data records were found below the header row."
        Exit Sub
    End If

    Set sourceRange = wsSource.Range(wsSource.Cells(1, 1), _
                                     wsSource.Cells(lastRow, lastCol))
    Set destinationRange = wsDestination.Range("A1").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Write values only; formulas and formatting are not transferred.
    destinationRange.Value = sourceRange.Value
End Sub

For example, if a source cell contains a formula that currently evaluates to 125, the values-only version writes 125 to the matching destination cell, not the formula. It also does not bring over the source’s number format, so a date or currency value may look different until the destination is formatted appropriately.

Run the macro and preserve your workbook

Before running either macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a copy operation can overwrite destination cells. The code writes from A1 on the Archive sheet and does not clear cells outside the copied rectangle; if an earlier run copied a larger range, old cells beyond the new range may remain.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the VBA code.
Confirm the source and destination worksheet names match the names in the code.
Open the Visual Basic Editor with Alt+F11, put the code in a standard module, and return to Excel.
Run the macro from the editor with the cursor inside it, or use Developer > Macros, select the macro, and choose Run.
Check the destination’s first and last rows and columns before relying on the copied data.

The Developer tab may need to be enabled in Excel’s ribbon settings. On Windows, use File > Options > Customize Ribbon and select Developer. Do not enable macros from an unknown workbook simply to make a copy operation work; only run code you trust and understand.

On a Mac, the Visual Basic Editor can be opened with Option+F11; some keyboards require Fn+Option+F11. The Developer tab and macro tools are also available in Excel for Mac, though their placement and keyboard behavior can differ. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros; use the desktop application.

Common copying problems and fixes

The last row is missing. Check whether the key column used in the End(xlUp) calculation has blanks. If it does, choose a column that is filled for every record. Also confirm that the new record is on the same worksheet referenced by the macro.

Only one column is copied. Check the header row. If the macro looks at row 1 but your headers are on row 3, it may calculate the wrong last column. Update the calculation to use the actual header row.

Old cells remain at the destination. Copying a smaller range does not erase cells left over from a previous, larger copy. If clearing old content is part of the intended process, decide exactly which destination area is safe to clear before adding a ClearContents operation. Clearing an entire sheet can destroy unrelated information.

Formulas show unexpected results. Relative cell references can change when copied to another location. If you need fixed results, use the values-only version. If formulas must remain formulas but retain their original references, review whether those references should be absolute or otherwise adjusted.

Filtered or hidden rows behave differently than expected. The sample defines a rectangular source range and does not explicitly select visible cells only. A request to copy only visible rows needs a separate approach, especially when filtered results form multiple separate areas. Test that behavior on a copy of the workbook before using it in a production process.

Use a Table or a named range instead

For a simple list, an Excel Table can reduce the need to calculate the edges yourself. Click inside the data and choose Insert > Table, confirming whether the range has headers. Tables expand when new rows are added and provide structured references that are easier to read than fixed cell addresses. VBA can refer to a table by its name and use its data or header ranges.

A dynamic named range is another option when a range needs a reusable name for formulas or other workbook features. It is not automatically better for a copy macro: VBA still needs a clear rule for what belongs in the range. Choose based on the task. Use a direct last-row/last-column calculation for a straightforward rectangular dataset, a Table for maintained tabular data, or a named range when a named reference is useful across the workbook.

For a repeated import-and-copy workflow, consider whether copying is needed at all. If the goal is to keep a destination synchronized with source data, formulas, Power Query, or a Table-based process may be more maintainable. VBA is useful when the workflow needs a button, a sequence of actions, or custom handling that those alternatives do not provide.

Frequently Asked Questions

How do I copy a range that changes size in Excel VBA?
Find the last row and column using a reliable rule, create a Range from the top-left cell to those boundaries, and copy it with Destination. The example assumes a filled key column and headers in row 1.

How do I copy only values with VBA?
Make a destination range with the same number of rows and columns as the source, then assign its Value property from the source range’s Value property. This transfers calculated values rather than formulas or formatting.

Can I use this macro if column A has blanks?
Not reliably if blanks occur before later records. Change the last-row calculation to use a column that is populated in every record, such as a unique ID column.

Why does the macro copy old data below the new range?
The sample writes the current range but does not clear cells beyond it. If the source shrinks, previous destination cells can remain. Clear only a carefully defined destination area if removing those old cells is intended.

Can I run VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop application to edit or run this code.

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.