VBA Copy and Paste Values Only: Practical Methods and Code

To copy values only with VBA, assign the source range’s values to an equally sized destination range: destinationRange.Value = sourceRange.Value. This writes the current results of formulas as values and does not copy the source cell formatting; use Excel’s PasteSpecial with xlPasteValues when you specifically want the paste operation approach.

What “values only” means in Excel

A cell can contain a constant, such as the number 25, or a formula, such as =A1+B1. The cell displays a result, but the formula remains in the formula bar. Copying values only replaces a formula with its current result. For example, copying a cell whose formula returns 25 produces a destination cell containing the number 25, not a formula that recalculates when its inputs change.

Values-only copying does not mean “copy everything that looks visible.” It normally leaves out formatting such as font, fill color, borders, number formats, and column width. The destination keeps its existing formatting. A numeric value pasted into a destination formatted as a date may therefore display as a date, even though the underlying value is still numeric.

Values can also include text, dates stored as numbers, logical values such as TRUE or FALSE, and error results such as #N/A. If a source formula currently returns an error, a values-only copy transfers that error result; it does not repair or recalculate the underlying cause.

Method 1: Assign values directly

Direct assignment is usually the simplest and fastest way to transfer values between ranges inside a macro. It does not use the clipboard. The following example copies A1:C10 on Sheet1 to A1 on Sheet2 in the workbook that contains the macro. Save a separate copy of the workbook before running it: VBA changes cannot be undone with Ctrl+Z.

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

    'ThisWorkbook must contain both named worksheets.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
    Set destinationRange = ThisWorkbook.Worksheets("Sheet2").Range("A1").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    'Write results, not formulas or source formatting.
    destinationRange.Value = sourceRange.Value
End Sub

The destination is resized to match the source’s row and column count, so it becomes a 10-row by 3-column range. Matching dimensions matter: assigning a multi-cell array to a destination of a different shape can produce an error or an unintended result. Direct assignment replaces the destination’s existing contents, but normally leaves its formatting intact.

For values-only transfers, .Value2 is also commonly used in place of .Value. It avoids VBA’s special Currency and Date subtypes and exposes those cell contents as numbers. Excel still applies the destination’s number format when displaying the result. Choose either property deliberately when your code depends on how dates or currency values are represented in VBA.

Method 2: Use PasteSpecial with xlPasteValues

Use PasteSpecial when you want code that follows Excel’s familiar copy-then-paste workflow, or when the destination is already selected as a specific cell. This example copies the same source range to a destination beginning at A1 on Sheet2.

Sub PasteValuesOnly()
    Dim sourceRange As Range
    Dim destinationCell As Range

    'The workbook containing this macro must have both worksheets.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
    Set destinationCell = ThisWorkbook.Worksheets("Sheet2").Range("A1")

    'Copy the range, then paste only its values.
    sourceRange.Copy
    destinationCell.PasteSpecial Paste:=xlPasteValues

    'Clear the moving-copy border and release copy mode.
    Application.CutCopyMode = False
End Sub

xlPasteValues is the Excel VBA constant for pasting values without the source formatting. The destination area must be large enough for the copied range. Unlike direct assignment, this method uses Excel’s copy mode and clipboard, so it can be useful when adapting a macro that already performs other kinds of paste operations. It is generally not necessary just to copy results between two ranges in the same workbook.

Choosing between the two methods

For a routine transfer within a macro, direct assignment is usually the clearest option. It avoids clipboard state and is efficient for large blocks of cells. Use PasteSpecial if matching a manual paste workflow makes the code easier to understand, or if the macro needs other paste types alongside values.

Choose direct assignment for straightforward range-to-range transfers where only cell contents matter.
Choose PasteSpecial when your process specifically uses Excel’s copy and paste behavior or has related paste steps.
Choose a different paste constant only when you intentionally need something else, such as formats. xlPasteValuesAndNumberFormats, for example, includes number formats as well as values.

Neither method copies formulas as formulas when configured for values only. Neither automatically copies column widths. If the destination is formatted differently from the source, the displayed appearance can differ even though the transferred value is correct.

Keep values in the same cells

A common request is to convert formulas in place, rather than copy them to another sheet. The direct-assignment pattern works when the source and destination are the same range. With formulas in A1:C10 on Sheet1, this macro replaces them with their current results:

Sub ReplaceFormulasWithValues()
    Dim targetRange As Range

    'This example changes formulas in place on Sheet1.
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")

    'Overwrite formulas with their current results.
    targetRange.Value = targetRange.Value
End Sub

This is a destructive change: after the macro runs, the formulas are gone. If formulas depend on other cells and Excel is set to manual calculation, the displayed results may not reflect the latest inputs. Calculate the relevant formulas before converting them if freshness matters. For example, targetRange.Calculate can calculate that range before the assignment, although formulas with dependencies outside it may require a broader calculation.

Run and save the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. Return to Excel and press Alt+F8, select the macro, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

Save a workbook containing VBA in the Excel Macro-Enabled Workbook format, with the .xlsm extension. Excel for the web can open an .xlsm workbook, but it cannot create, edit, or run VBA macros; use the Excel desktop application. Excel for Mac supports VBA in the desktop app, though keyboard shortcuts can vary with the keyboard and function-key settings. Use the Visual Basic Editor and Macros commands from Excel’s menus if the Windows shortcuts do not work.

Only run macros you understand and trust. A macro can overwrite data without showing the usual Undo history, so make a backup before running code that changes cells. Also check the worksheet names and ranges in the example: if your workbook uses different names, replace Sheet1, Sheet2, and the sample addresses with the correct ones.

Common problems and how to avoid them

“Subscript out of range”: A referenced worksheet name does not match a tab name in the workbook. Check spelling, spaces, and punctuation.
Wrong workbook is changed: ThisWorkbook means the workbook where the VBA code is stored. If the macro is in Personal.xlsb or an add-in, that may not be the workbook holding your data. Qualify the intended workbook explicitly rather than relying on the active workbook.
Destination values appear as dates: The destination may have a date number format. Change its number format if you want to see the number as a number; the underlying copied value may still be correct.
Source formulas turn into errors: The macro copies the formula’s current result, including an error result. Investigate the source formula or its inputs before copying if errors are not expected.
Only part of the data is copied: Confirm that the source address covers the full intended range and that the destination is resized to the same dimensions.
Nothing runs in a browser: VBA is a desktop Excel feature. Open the workbook in Excel for Windows or Mac to run the macro.

When a transfer should include only the visible rows of a filtered list, decide explicitly whether hidden rows should be included. A simple range-to-range assignment covers the specified range, including cells that may not be visible because of filtering. Filtered and discontiguous selections can need additional handling; do not assume that a basic copy macro will behave exactly like selecting visible cells manually.

Frequently Asked Questions

How do I paste values only in VBA? Copy the source range and use destinationCell.PasteSpecial Paste:=xlPasteValues, or assign destinationRange.Value = sourceRange.Value. The assignment method is often simpler when the source and destination are ordinary ranges in the same workbook.

Does VBA copy the formula result or the formula? When you assign a range’s Value or Value2, VBA transfers the current cell contents as values, so a formula’s current result is written instead of its formula. If you use a normal copy operation without a values-only paste, formulas may be copied instead.

How do I convert formulas to values in place? Set a range variable to the cells you want to change, then assign the range to itself, such as targetRange.Value = targetRange.Value. This permanently replaces formulas in that range, so save a backup first.

Will values-only VBA keep the formatting? Direct value assignment leaves existing destination formatting in place, but does not copy the source formatting. xlPasteValues likewise pastes values rather than source formats. To preserve source number formats as well, choose an appropriate paste type such as xlPasteValuesAndNumberFormats.

Can I run a VBA values-only macro in Excel for the web? No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel for Windows or Mac to use VBA.

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.