Copy and Paste Dynamically Using VBA in Excel

To copy a range that changes size as your data grows, use VBA to find its last row and column, build a range from those limits, and copy it to a destination. The example below reads a table from a worksheet named Data and copies it to Report. It assumes the headings are in row 1 and column A contains a value for every data row.

What You’ll Need

A desktop version of Excel for Microsoft 365 on Windows, with a workbook containing the source data. Excel for the web cannot run VBA macros.
A source worksheet named Data and a destination worksheet named Report, or the names of your own sheets ready to substitute in the code.
A macro-enabled workbook saved as an Excel Macro-Enabled Workbook (.xlsm). Save a separate copy before running a macro: VBA actions cannot be reversed with Ctrl+Z.

Step-by-Step

Arrange the source data. Put field headings in row 1, beginning in A1. Keep each record on one row, and make sure column A is filled for every record. The macro uses the last nonempty cell in column A to decide how many rows to copy. It looks across row 1 to find the last heading and uses that to decide how many columns to include. Avoid blank headings between populated columns, because the code finds the last heading, not the last column in every individual record.
Prepare the destination. Add a worksheet named Report if you do not already have one. Decide where the copied range should start; the sample uses A1. Keep other important information away from the output area. The macro writes into Report but does not erase old content outside the size of the newly copied range.
Save a working copy as .xlsm. In Excel for Windows, choose File > Save As, select a folder, and choose Excel Macro-Enabled Workbook (*.xlsm) in the file type list. If Excel displays a format warning, choose the option to keep the macro-enabled format. A regular .xlsx file cannot store VBA code, so saving in that format removes the macro.
Open the Visual Basic Editor. On Windows, press Alt+F11. You can also choose Developer > Visual Basic if the Developer tab is visible. To show it, use File > Options > Customize Ribbon and select Developer. On Mac, use Tools > Macro > Visual Basic Editor, or the Developer tab if available; the keyboard shortcut can depend on your keyboard and may require Fn with Option+F11.
Insert a standard module. In the Visual Basic Editor, find your workbook in the Project pane. Choose Insert > Module. A blank code window opens. A standard module is a suitable place for a macro you intend to run manually; do not paste this procedure into a worksheet code window.
Paste in the macro. Copy the complete procedure below into the module. The variable declarations identify the worksheets, row and column limits, source range, and destination cell. The comments explain the key actions. Change the worksheet names in quotation marks if your tabs have different names.

Sub CopyDynamicRange()
    ' Copy a changing data block from Data to Report.
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim sourceRange As Range
    Dim destinationCell As Range

    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Report")

    ' Find the final record in column A and final heading in row 1.
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

    ' Stop if there are no headings in the first row.
    If Application.WorksheetFunction.CountA(wsSource.Rows(1)) = 0 Then
        MsgBox "No headings were found in row 1."
        Exit Sub
    End If

    Set sourceRange = wsSource.Range(wsSource.Cells(1, 1), _
                                     wsSource.Cells(lastRow, lastCol))
    Set destinationCell = wsTarget.Range("A1")

    ' Copy cells, including formulas and formatting, to the destination.
    sourceRange.Copy Destination:=destinationCell
End Sub

Check the worksheet and column assumptions. If your records are identified by a different column, change the “A” in the lastRow line to that column’s letter. If headings are on another row, update both uses of row 1: the lastCol calculation and the heading check. Adjust the first cell used to build sourceRange if your data starts somewhere other than A1. The sample deliberately copies the header row as well as the records.
Run the macro. Return to Excel, press Alt+F8 on Windows, select CopyDynamicRange, and choose Run. On Mac, open Tools > Macro > Macros, select the procedure, and run it; shortcuts vary by keyboard. If Excel shows a security warning, only enable macros for a workbook you trust. Look at Report and confirm the copied range starts in A1 and includes the expected final row and column.
Choose values-only behavior if needed. The Copy Destination line copies formulas as formulas, along with cell formatting. If the destination should receive calculated results only, replace that line with the two lines below. They resize the destination to match the source and assign the cell values without copying formulas or formatting. The line in the main macro that sets destinationCell must remain in place.

    ' Use this instead of sourceRange.Copy for values only.
    destinationCell.Resize(sourceRange.Rows.Count, _
                           sourceRange.Columns.Count).Value2 = sourceRange.Value2

For example, if Data contains headings in A1:C1 and records through row 25, the macro builds A1:C25 and places it at Report!A1. If a new record is added in row 26 and column A is populated, the next run includes it without changing the code. If the added field has a heading in D1, the next run also includes column D. The size is recalculated each time the macro runs; it does not update the destination continuously while you type.

Common Mistakes

Using a column with gaps to find the last row. End(xlUp) finds the last filled cell in the chosen column. If column A has blanks in the middle, later records are still included when the final record has an A value, but rows below the last filled A cell are missed. Use a consistently populated key column, such as an ID, or revise the method if no column is consistently filled.
Leaving sheet names unchanged when yours differ. Worksheet names in the code must match the tab names exactly, including spaces. A name mismatch produces a “Subscript out of range” error. Update both names in the Set statements.
Expecting a values-only copy from Range.Copy. Copy Destination carries formulas as well as cell content and formatting. Use the Value2 assignment shown above when you need fixed results, such as a snapshot that should not recalculate.
Assuming the destination is automatically cleaned. A smaller new range does not necessarily remove old cells that were part of a larger previous output. If the destination is a dedicated output area, clear that area deliberately before copying. Do not add a broad Clear command without checking that it will not delete unrelated data.
Running an unsaved or untrusted macro. Save the workbook as .xlsm and enable macros only when you understand and trust the file. Keep a backup because VBA changes cannot be undone with Ctrl+Z.

Tips

Use descriptive worksheet names and constants if you expect to maintain the code. For a simple first macro, changing the two worksheet names in the Set statements is enough; be sure the destination tab exists before running it.
If your source range begins below row 1 or to the right of column A, build the range from the correct starting cell rather than copying the blank area above or beside the data. The sample is designed for a contiguous table beginning at A1.
If formulas refer to other cells, copying them to a different position may adjust relative references, just as a normal Excel copy does. If you need fixed references or just the displayed results, choose the values-only method or adjust the formulas for your intended destination.
For an output area that should contain only the latest result, decide how to handle old rows before automating a clear. One safe approach is to use a dedicated destination sheet and verify the cells it contains before clearing them. Avoid clearing an entire worksheet if it also stores notes, formulas, or other reports.
Test with a small sample first. Compare the first and last included rows and columns, check whether formulas or values are expected, and only then run the macro on the full workbook. This catches incorrect assumptions about the header row or the key column early.

Troubleshooting

“Subscript out of range” appears: Check that the source and destination worksheet tabs exist and match the names in the code exactly. The workbook must also be the workbook that contains those sheets, because the sample uses ThisWorkbook.

The macro copies only one row or stops too early: Inspect the column used in the lastRow calculation. It must contain a value in the final record. If your data has a different reliable key column, change the column letter in that line. Also check that new rows are inside the source worksheet and not on another tab.

The last field is missing: The macro finds the last nonempty heading in row 1. Put a heading above each column you want copied, remove unintended empty gaps in the heading row, or adapt the code to use a different header row. Data below a blank heading may be outside the calculated source range.

Nothing happens or Excel blocks the macro: Confirm the file is .xlsm, the macro is in a standard module, and you ran CopyDynamicRange from the macro list. In Windows, review Excel’s Trust Center macro settings; do not lower security settings indiscriminately. If the workbook came from the internet, Windows may mark it as blocked, so check the file’s security properties and your organization’s policies.

The output contains formulas when you wanted fixed numbers: Replace the Copy Destination statement with the Value2 assignment in Step 9. Conversely, if formulas are expected but the result is only numbers, make sure you selected the Copy Destination version rather than the values-only version. Remember that formatting is not included with a Value2 assignment.

Excel for the web does not run the procedure: Open the workbook in the desktop Excel app for Windows or Mac. VBA macros cannot run in Excel for the web. The macro can still be stored in an .xlsm workbook, but use the desktop app to edit or execute it.

Frequently Asked Questions

Can I copy a dynamic range without using Select or Activate?
Yes. The example sets the source and destination ranges directly and uses Copy Destination. This is generally more reliable than selecting cells first, because it does not depend on which worksheet or cell is active.

Does the macro update the copy immediately when I add data?
No. It calculates the last row and column whenever you run it. Run it again after adding records to refresh the destination. If you need an automatically refreshed report, consider whether an Excel Table, a formula-based approach, or a deliberately triggered macro better fits the task.

Can I copy to a different starting cell?
Yes. Change the destination in the Set destinationCell line. For example, use Range(“B3”) to start at B3 on Report. The copied range will extend right and down from that cell, so make sure it will not overwrite anything important.

Can I copy only selected columns?
Yes, but the sample copies one continuous rectangle from column A to the last heading. For specific columns, define those source columns separately and copy each to its intended destination, or revise the source-range construction. Check the destination layout carefully when columns are not adjacent.

Will this work on a Mac?
The VBA code uses standard Excel worksheet and range objects and is suitable for desktop Excel for Mac as well as Windows. The route to the Visual Basic Editor and macro list differs, and shortcut keys can depend on the Mac keyboard. Excel for the web does not execute VBA.

Can I undo the macro with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, and Ctrl+Z cannot reverse the macro’s changes. Save a separate copy before running it, particularly before adapting it to clear or overwrite a destination area.

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.