Excel VBA: Copy and Paste Range Values

To copy a range’s results without its formulas or formatting, assign the source range’s Value property to a same-sized destination range’s Value property. This copies the current cell values directly, without using Excel’s clipboard.

How values-only copying works

A cell can contain a formula, a displayed result, and formatting. For example, a cell containing a formula that adds two numbers has a formula in the formula bar and a calculated number in the grid. A values-only copy transfers that calculated result, not the formula that produced it. If the source cell contains typed text, a number, or a date, that cell content is transferred as a value.

Range-to-range assignment is usually the simplest way to do this in VBA. It works directly between the two ranges, so it does not need the clipboard, selecting cells, or activating a worksheet. The source and destination should have the same number of rows and columns for a predictable cell-by-cell transfer.

Source formulas become their current results in the destination.
Existing destination formatting is not replaced.
The source range is left unchanged.
The destination’s existing contents are overwritten.

This is different from copying and pasting normally. A standard paste can bring formulas, formatting, and other cell features along with the contents. A values-only operation is useful when you need a fixed snapshot, such as a report extract or a record to send to another workbook.

Basic VBA example for a fixed range

The macro below copies cells A1:C10 from Sheet1 to the same-sized area on Sheet2 in the workbook containing the macro. Change the worksheet names and addresses to match your workbook.

Sub CopyRangeValues()
    Dim sourceRange As Range
    Dim destinationRange As Range

    ' Use explicit worksheets rather than the active sheet.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
    Set destinationRange = ThisWorkbook.Worksheets("Sheet2").Range("A1:C10")

    ' Transfer values only; formulas are not carried over.
    destinationRange.Value = sourceRange.Value
End Sub

ThisWorkbook means the workbook that contains this VBA code. Using it is safer than relying on whichever workbook happens to be active when the macro runs. If the code is stored in a personal macro workbook or an add-in, ThisWorkbook refers to that file instead; in that situation, set a workbook variable to the intended file or use an explicitly identified open workbook.

Before running a macro that writes to cells, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so the normal Excel undo history will not restore the overwritten destination values.

Use PasteSpecial when you need the clipboard method

VBA can also perform the equivalent of Excel’s Paste Special, Values command. This approach uses the clipboard, but it can be useful when adapting code that already copies ranges or when you want the macro to mirror a manual copy-and-paste workflow.

Sub CopyRangeValuesWithPasteSpecial()
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
    Set destinationRange = ThisWorkbook.Worksheets("Sheet2").Range("A1:C10")

    ' Copy, then paste only values into the destination.
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

xlPasteValues tells Excel to paste values rather than formulas or formats. Clearing Application.CutCopyMode removes the moving copy border and ends copy mode. For a straightforward transfer, direct range assignment is generally shorter and avoids clipboard state. Do not substitute destinationRange.Copy Destination:=sourceRange for a values-only operation: that is a regular copy and can transfer more than values.

Copy a range whose size changes

If the number of records varies, calculate the last used row before setting the ranges. This example finds the last non-empty cell in column A, copies columns A through C from row 1 down to that row, and writes the result starting at E1 on another sheet.

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

    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
    Set destinationSheet = ThisWorkbook.Worksheets("Sheet2")

    ' Assumes column A is populated for every record to include.
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    Set sourceRange = sourceSheet.Range("A1:C" & lastRow)
    Set destinationRange = destinationSheet.Range("E1").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    destinationRange.Value = sourceRange.Value
End Sub

This pattern includes row 1, which is often a header. It also relies on column A being filled wherever a record should count; if records can have blank cells in column A, choose a more reliable column or determine the last row using a method suited to the data. If there is no data, the example can still treat row 1 as the range, so add a check if an empty sheet should result in no copy.

Resize sets the destination dimensions to match the source. That matters because a source and destination with different shapes may produce an error or an unintended result. The destination’s top-left cell is E1 here; changing that starting address moves the output without changing its size.

Choosing Value or Value2

For most values-only transfers, Value is clear and sufficient. Excel’s Value2 property is another option. It avoids returning VBA Currency and Date subtypes for certain cell values; instead, dates and currency-formatted numbers are represented as numeric values. Excel stores dates as serial numbers, and the cell’s number format controls whether a number appears as a date or as a plain number.

Use Value2 when you specifically want to move the underlying numeric values without the Date or Currency subtype conversions that can occur with Value. For example, a date may be transferred as its serial number while the destination’s existing date format determines how it displays. If the destination is formatted as General, that serial number may appear as a number rather than a date. Neither property copies the source cell’s formatting.

For ordinary worksheet values, choose one property and use it consistently. Do not expect either property to preserve formulas: both transfer evaluated cell contents rather than formula text. If you need the formulas, assign the Formula property instead, or use an appropriate formula-copy method.

Common problems and practical safeguards

When a macro appears to copy the wrong data or produces an error, check the range references and assumptions before changing the copy method. These are frequent causes:

Wrong active sheet: Unqualified references such as Range("A1:C10") can depend on the active worksheet. Qualify ranges with a worksheet variable or workbook-and-sheet reference.
Different range sizes: Make the destination the same height and width as the source, especially when copying a dynamically sized range.
Protected destination: Worksheet protection may prevent writing to the destination cells. Confirm the sheet is writable or handle protection appropriately.
Merged cells: Merged areas can make range dimensions and assignments difficult to match. Avoid merged cells in data blocks where possible.
Unexpected blanks in the last-row column: A last-row calculation based on column A will stop at the last non-empty cell in A, not necessarily the last record elsewhere in the table.
Filtered or hidden rows: Assigning values between full rectangular ranges does not automatically mean “copy only the visible rows.” If the requirement is visible cells only, handle visible cells deliberately; non-contiguous visible areas need extra care.

Also consider what should happen to cells left behind when the new source range is smaller than an earlier one. The examples overwrite the destination cells covered by the current range but do not clear any extra cells beyond it. Clear those cells separately only if that is part of the intended workflow.

How to add and run the macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the macro into the module, and edit the worksheet names and ranges. Return to Excel and run a macro with Alt+F8, select its name, and choose Run. If the Developer tab is already visible, Developer > Visual Basic opens the editor.

On a Mac, the Visual Basic Editor shortcut is usually Option+F11; some keyboards require Fn+Option+F11. Keyboard settings can affect the shortcut, so use Excel’s menu or ribbon if needed. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop Excel application.

To keep VBA in a workbook, save it as an Excel Macro-Enabled Workbook (.xlsm). Excel may display a security warning when the file is opened. Enable macros only when you trust the workbook and its source. Saving a copy before running a write macro is especially important because Ctrl+Z cannot undo its changes.

Frequently Asked Questions

How do I copy a range as values in Excel VBA?
Set a source and an equally sized destination range, then assign the source range’s Value property to the destination range’s Value property. This transfers current results without copying formulas or formatting.

Does assigning Value copy formulas?
No. It copies the values currently returned by formulas. To transfer formulas rather than their results, use a formula-copy method or assign the source formula property to the destination.

Does a values-only copy keep the source formatting?
No. Direct value assignment leaves the destination’s existing formatting in place. Use a separate formatting operation if you also want to copy appearance.

Can I copy values between worksheets or workbooks?
Yes. Use fully qualified references to the source and destination worksheets. For another workbook, identify the intended workbook explicitly; both workbooks generally need to be open for direct range-to-range assignment.

Why can’t I undo a VBA values copy?
Running a macro clears or bypasses Excel’s ordinary undo history. Save a separate copy before running a macro that overwrites cells, and verify the source and destination references first.

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.