Copy and Paste Values in VBA: Methods and Examples

To copy only cell values with Excel VBA, assign one range’s Value2 to another range of the same size, such as destinationRange.Value2 = sourceRange.Value2. To use Excel’s paste operation instead, copy the source and call destinationRange.PasteSpecial Paste:=xlPasteValues; both methods leave the source formulas behind and put their calculated results in the destination.

What “values only” means in Excel

A cell that displays 25 might contain the number 25, a formula such as =10+15, or a date represented by a number. Copying values only transfers the cell’s underlying result, not its formula. If the source contains a formula, the destination receives the current calculated result rather than a formula that will recalculate from other cells.

Values-only copying does not normally copy the source’s font, fill, borders, conditional formatting, data validation, or column width. The destination keeps its existing formatting. A date may therefore arrive as a number and look different if the destination has a different number format. For a values-only paste that also brings over number formats, use xlPasteValuesAndNumberFormats instead of xlPasteValues.

Choose between direct assignment and PasteSpecial

Direct assignment is usually the simplest option when a macro moves data between known ranges. It does not use the clipboard, so it avoids copy mode and is often efficient for large blocks. Use it when the source and destination have matching dimensions and you want the results, including formula results, transferred without formatting.

PasteSpecial is useful when you want VBA to behave more like Excel’s Paste Special command, or when you need one of its other paste options. It uses Excel’s copy operation, so the clipboard and copy mode are involved. For a straightforward transfer of values, either approach works; choose based on whether you need a specific paste option or a direct range-to-range transfer.

Use direct assignment for predictable, same-sized ranges and a quick transfer without clipboard operations.
Use PasteSpecial when you need Excel’s paste options, such as values plus number formats.
Use an in-place assignment when you deliberately want to replace formulas in a range with their current results.

Basic macro: transfer values to another range

This example copies the results in cells A2:C20 on a worksheet named Data to E2:G20 on that same worksheet. Adjust the sheet name and addresses for your workbook. The ranges are both 19 rows by 3 columns, so each source cell has a matching destination cell.

Sub CopyValuesDirectly()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    ' Set ranges on the worksheet containing the data.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("A2:C20")
    Set destinationRange = ws.Range("E2:G20")

    ' Transfer results without copying formulas or formatting.
    destinationRange.Value2 = sourceRange.Value2
End Sub

In this code, ThisWorkbook means the workbook that contains the VBA code. If the macro is stored in PERSONAL.XLSB or another workbook, it does not automatically refer to the workbook currently visible on screen. Set an explicit workbook reference when the code and data are in different workbooks. Also check that the destination is safe to overwrite: this assignment replaces whatever values or formulas are already in E2:G20.

Use PasteSpecial to paste values only

The next example copies the same source range and pastes its values into a destination range. It clears Excel’s copy mode when the paste is finished. The source and destination must be on worksheets in the same open Excel instance; here they are on separate sheets in the workbook containing the macro.

Sub PasteValuesOnly()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    ' Identify the source and destination ranges.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Archive")
    Set sourceRange = sourceSheet.Range("A2:C20")
    Set destinationRange = destinationSheet.Range("A2:C20")

    ' Copy calculated values, then leave Excel out of copy mode.
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

The destination does not have to be on the same sheet as the source. Confirm that the destination range has enough rows and columns for the copied block and that the intended cells will not be overwritten. If a workbook is protected, merged cells or protection settings can also prevent a paste from completing as expected.

Replace formulas with their current results

A common related task is converting formulas to fixed values in place. This is different from copying results to a separate destination: it removes the formulas from the selected range. The assignment below reads the range’s current values and writes those values back into the same cells.

Sub FreezeRangeAsValues()
    Dim ws As Worksheet
    Dim targetRange As Range

    ' Select only the cells whose formulas should be replaced.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set targetRange = ws.Range("D2:D100")

    ' Replace formulas with their current results.
    targetRange.Value2 = targetRange.Value2
End Sub

After this runs, formulas in D2:D100 are gone, and later changes to their input cells will not update those results. This can be useful for a static report or an export snapshot, but it is not a way to preserve a live calculation. Save a separate copy before running a macro that overwrites data; VBA actions generally cannot be undone with Ctrl+Z.

Copying to a differently formatted destination

Values and display formats are separate. For example, a source cell might contain the numeric date serial 46,000 and use a date number format, while the destination is formatted as General. A values-only transfer can put the same underlying number in the destination but show 46000 rather than a calendar date. Apply a suitable number format separately if the destination needs the same appearance.

If the goal is to preserve number formats while still excluding formulas, use this PasteSpecial option:

Sub PasteValuesAndNumberFormats()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    ' Use matching ranges so each cell has a destination.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("A2:C20")
    Set destinationRange = ws.Range("E2:G20")

    ' Paste results and number formats, but not formulas.
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub

This option transfers number formats, not every aspect of the source’s appearance. It does not make the destination a complete visual duplicate. If you need all formatting, formulas, widths, or other worksheet features, use the relevant paste type or copy the range for that specific purpose rather than assuming “values” includes them.

Common problems and practical checks

Nothing happens or Excel reports a range error: Check that the worksheet names are spelled exactly as they appear on the sheet tabs and that the ranges refer to valid cells.
Results appear in the wrong workbook: Remember that ThisWorkbook is the workbook containing the macro. Use an explicit workbook variable if the data is elsewhere.
Formulas remain in the destination: Confirm that the macro is targeting the intended cells and using Value2 assignment or xlPasteValues, not a normal copy or formula paste.
Dates or amounts look different: Values-only transfer does not copy ordinary number formats. Check the destination’s number format, or use the values-and-number-formats paste type when appropriate.
Only part of the data is transferred: Verify the source and destination dimensions. For direct assignment, use ranges with matching row and column counts.
The macro cannot write to the destination: Check whether the sheet is protected, the workbook is read-only, or the destination includes merged cells that conflict with the source layout.

For large datasets, transfer a whole block at once instead of looping through cells one at a time. Cell-by-cell code can be slower because it repeatedly communicates with the worksheet. If you must process individual cells, make sure the loop’s row and column boundaries match the data and restore any application settings that the macro changes.

Running and safeguarding a VBA macro

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the macro into the module. Return to Excel, save the file as a macro-enabled workbook (.xlsm) if it needs to retain the VBA, and run the macro from the editor or through Developer > Macros. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

On a Mac, open the Visual Basic Editor with Option+F11 (some keyboards require Fn as well), or use Tools > Macro > Visual Basic Editor. The ribbon and shortcut details differ by keyboard and Excel setup, but the VBA range methods shown here are generally the same. Excel for the web can open and edit workbooks with macros, but it cannot create, run, or edit VBA macros; use the desktop application to execute them.

Only run macros from sources you trust. Before a values-only macro runs, save a separate copy of the workbook and review the source and destination addresses. This is especially important for in-place conversion, because formulas are replaced and Ctrl+Z will not restore the prior formulas.

Frequently Asked Questions

How do I copy a formula’s result instead of the formula in VBA?
Assign the source range’s Value2 to a same-sized destination range, or use PasteSpecial Paste:=xlPasteValues. Both transfer the current result rather than the formula.

What is the difference between Value and Value2 in VBA?
Both can read and write cell contents. Value2 does not use VBA’s Currency and Date subtypes; dates are handled as their underlying numeric serial values. Since number formatting is separate, the destination’s format determines how that number appears.

Does PasteSpecial values copy formatting?
xlPasteValues does not copy ordinary formatting such as fonts, fills, or borders. Use xlPasteValuesAndNumberFormats if you want to include number formats as well as the values.

Can I paste values between worksheets?
Yes. Set the source and destination ranges on their respective worksheets, then use direct assignment or copy followed by PasteSpecial. Confirm both sheets belong to the intended open workbook or use explicit workbook references.

Can I undo a VBA values-only paste with Ctrl+Z?
Usually not. Running a macro clears Excel’s normal undo history, so save a backup copy first, especially before replacing formulas or overwriting existing 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.