Excel VBA Copy and Paste Range: Examples and Tips

In Excel VBA, copy a range to another location with sourceRange.Copy Destination:=destinationRange. For values only, assign the ranges directly with destinationRange.Value2 = sourceRange.Value2; this avoids copying formulas, formatting, and other cell features.

Copy and paste a range with VBA

The Copy method is the straightforward choice when you want to reproduce a block of cells, including its formulas and formatting. The destination argument tells Excel where the copied range should begin, so the macro does not need to select cells or rely on the clipboard.

For example, this macro copies cells A1:D20 from a worksheet named Data to A1 on a worksheet named Report. It assumes both worksheets are in the workbook containing the macro:

Sub CopyRangeBasic()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    ' Copy the block, including its formulas and formatting.
    sourceSheet.Range("A1:D20").Copy _
        Destination:=destinationSheet.Range("A1")
End Sub

The source and destination ranges need not have the same address, but the destination must provide enough room for the copied block. A four-column by twenty-row source pasted at A1 fills A1:D20. Because the worksheets are explicitly qualified, the result does not depend on which sheet is active when the macro runs.

ThisWorkbook means the workbook that contains the VBA code. If the source data is in a different, already-open workbook, refer to that workbook explicitly instead. Avoid unqualified expressions such as Range("A1:D20") in reusable macros: they refer to the active worksheet, which can change unexpectedly.

What a normal copy includes

A regular range copy carries more than displayed text. Depending on the copied cells, it can transfer formulas, values, number formats, fill colors, borders, comments or notes, and other cell-level properties. Relative references in formulas normally adjust for the new location, just as they do when copying cells manually. For example, a formula referring to A2 may refer to A3 after being copied one row down.

That behavior is useful for making a formatted report or duplicating a worksheet section, but it may be wrong when the destination should contain fixed results rather than live formulas. It can also bring along formatting that does not fit the destination. Decide what the copied cells should mean before choosing the VBA operation:

Use Copy Destination:= when the destination should receive formulas and the usual cell formatting along with the content.
Assign Value2 when you want the current cell results only, with no formulas or formatting.
Use PasteSpecial when you need a particular subset, such as values or formats.

One important distinction: copying cell contents does not necessarily reproduce everything that makes a worksheet look identical. Column widths, row heights, merged-cell layouts, and objects may require separate handling. Check the output if visual layout is part of the requirement.

Copy values without formulas or formatting

When the source and destination ranges have the same dimensions, direct assignment is usually the clearest approach for values. It writes the calculated results into the destination without using Excel’s copy-and-paste clipboard. The destination’s existing number formats, borders, and fills remain in place.

Sub CopyValuesOnly()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    ' Transfer results from A1:D20, not formulas or formats.
    destinationSheet.Range("A1:D20").Value2 = _
        sourceSheet.Range("A1:D20").Value2
End Sub

Value2 transfers the underlying cell values without the VBA date and currency conversions associated with the older Value property. Excel still displays numbers according to the destination’s number format. Thus, if a source cell contains a date serial and the destination is formatted as General, the destination may show a number rather than a date; apply the appropriate number format if the display matters.

Direct assignment does not reproduce blank cells as a full copy operation might: assigning a blank source value clears the corresponding destination value, but does not clear its formatting. It is also best suited to rectangular ranges whose dimensions match. If the goal is to append values under existing data or to preserve blank formatting distinctions, define that behavior deliberately rather than assuming the assignment is a full paste.

Copy between worksheets or workbooks

To copy between worksheets in the same workbook, qualify both ranges with their worksheet variables, as in the first example. This works whether the sheets are visible or not. The source and destination worksheets must exist under the names used in the code; a renamed or misspelled tab produces a run-time error.

For another open workbook, set a workbook variable and then refer to one of its sheets. For example, Set destinationBook = Workbooks("Archive.xlsx") can identify an open workbook by its displayed file name. The file must already be open for this reference to work. If it is not open, use Workbooks.Open with a valid file path, and consider how the macro should handle missing files, read-only access, and closing the workbook afterward.

For workbooks saved in different locations, a full file path is less ambiguous than relying on the active workbook. Also distinguish ThisWorkbook from ActiveWorkbook: the former is where the macro is stored; the latter is whichever workbook currently has focus. A macro stored in Personal.xlsb or an add-in may act on a different workbook than a macro stored inside the data file, so explicit workbook references help prevent mistakes.

Copy a range whose last row changes

For a list that grows over time, a fixed endpoint such as row 20 will eventually omit new records. A common pattern finds the last nonblank cell in a key column, then builds a range from the header or first data row to that endpoint. The following example copies columns A through D, starting at row 2, and assumes column A is filled for every record:

Sub CopyChangingList()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim lastRow As Long
    Dim sourceRange As Range

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    ' Find the last populated row in the key column.
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    Set sourceRange = sourceSheet.Range("A2:D" & lastRow)
    sourceRange.Copy Destination:=destinationSheet.Range("A2")
End Sub

The check for fewer than two rows prevents the macro from treating a header-only sheet as a data list. Change the starting row if your layout differs. The last-row method depends on column A being a reliable key: if records can have a blank in A while another column contains data, use a more suitable column or a different method. Formatting or stray entries far below the list can also affect how the data is organized, so inspect the sheet structure before relying on a dynamic range.

This example pastes new output starting at Report!A2 each time. It does not automatically remove old rows left over from a previous, longer list. If stale results are a risk, clear the intended output area first, taking care not to erase headers or unrelated content.

Use PasteSpecial for selected content

When you need to retain the clipboard-based copy workflow but transfer only one type of content, use PasteSpecial. For example, this copies calculated values but not the source formulas or formats:

Sub PasteValuesOnly()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    sourceSheet.Range("A1:D20").Copy
    destinationSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Other built-in paste types include xlPasteFormats for formats and xlPasteFormulas for formulas. Constants such as these make the intent more readable than a numeric argument. PasteSpecial can be helpful when combining content and formatting in separate operations, but it uses Excel’s copy mode. Clear that mode after the paste if the moving border or clipboard state is no longer needed.

PasteSpecial does not mean “copy every possible worksheet feature.” If the task involves column widths or row heights, use the relevant paste type or set those dimensions separately. Test on a small representative range first, especially when the destination contains formulas or existing formatting that should be preserved.

Choose the right method

The best technique depends on whether you are duplicating cells or transferring data. A normal copy is closest to a user pressing Ctrl+C and Ctrl+V. Direct value assignment is often simpler and faster for a plain data transfer, particularly for large rectangular blocks, because it avoids selecting cells and does not need clipboard operations. PasteSpecial sits between those choices when only formulas, values, or formats should move.

Duplicate a formatted report section: use Copy Destination:=, then review relative formulas and layout.
Freeze current results as data: use a same-sized Value2 assignment.
Keep formulas but not the source appearance: use PasteSpecial Paste:=xlPasteFormulas.
Apply a consistent appearance: use PasteSpecial Paste:=xlPasteFormats, or set formatting explicitly.
Move a changing list: calculate its last row and guard against an empty list.

Copying and moving are not the same operation. Copy leaves the source intact. A cut-and-paste workflow moves cells, and can shift or overwrite nearby content. For a macro that should remove source data after transferring it, decide whether to clear only the source contents or delete whole rows; those actions have different effects on formulas, formatting, and neighboring records.

Common errors and how to avoid them

A “Subscript out of range” error often means a workbook or worksheet name does not match an open workbook or existing tab. Check spelling, extensions in workbook names, and whether the expected file is open. A method or object error can arise when a range is not correctly qualified or when the intended destination is protected or otherwise unavailable.

Run the macro safely on Windows and Mac

On Windows, open the VBA editor with Alt+F11. In Excel for Microsoft 365, you can also use Developer > Visual Basic; if the Developer tab is missing, enable it through File > Options > Customize Ribbon. On Mac, use Option+F11 (some keyboards require Fn+Option+F11), or open the editor from Tools > Macro > Visual Basic Editor. The VBA examples here use standard Excel object-model operations and are generally applicable on both platforms, though file paths and some keyboard shortcuts differ.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and a paste can overwrite cells without a convenient recovery step. On Mac, the undo shortcut is Command+Z, but it does not reverse VBA operations either. Use a test copy when the source workbook matters, and confirm the destination before executing a macro.

Macros must be saved in a macro-capable file such as .xlsm if they are to remain with the workbook. Excel may block macros based on security settings or file origin. On Windows, macro controls are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Do not enable macros in a file simply because Excel prompts you to; only run code from a source you trust. If your organization manages these settings, follow its policy.

Frequently Asked Questions

How do I copy a range in VBA without selecting it?

Qualify the source and destination ranges, then use sourceRange.Copy Destination:=destinationRange. The macro does not need to activate a worksheet or select cells, which makes it less dependent on the user’s current view.

How do I paste values only in Excel VBA?

For same-sized ranges, set the destination’s Value2 property equal to the source’s Value2 property. Alternatively, copy the source and use PasteSpecial Paste:=xlPasteValues when a paste operation is more suitable.

Does VBA copy formulas and formatting too?

Yes. A regular range copy generally transfers formulas, values, and cell formatting. Formulas with relative references adjust to the destination location. Use direct value assignment or a specific PasteSpecial option when you want to limit what transfers.

How can I copy only the used rows?

Find the last populated row in a dependable key column with Cells(Rows.Count, "A").End(xlUp).Row, then build the source range using that row number. Add a check for an empty or header-only list, and choose a column that is consistently filled for every record.

Can I copy a range to another workbook?

Yes, if you explicitly refer to the destination workbook and worksheet. The other workbook must be open when you reference it by name; otherwise, open it using its file path first. Check that you are not confusing the workbook containing the macro with the workbook that contains the 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.