Excel VBA Copy and Paste Values Only

To copy a range in Excel VBA and paste only its values, use Copy followed by PasteSpecial Paste:=xlPasteValues. For a direct transfer that does not use the clipboard, assign the source range’s Value2 to the destination range’s Value2; both approaches copy formula results rather than formulas.

The basic VBA method

This example copies cells A2:C20 from one worksheet to another, pasting the values into a destination range of the same size. Before running a macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

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

    ' Set the source and same-sized destination ranges.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:C20")
    Set destinationRange = ThisWorkbook.Worksheets("Sheet2").Range("A2:C20")

    ' Copy calculated results and constants, not formulas or formatting.
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Change the worksheet names and addresses to suit your workbook. The source and destination should have matching dimensions for a predictable result. ThisWorkbook means the workbook that contains the macro; if the code is stored in the Personal Macro Workbook, it will not automatically refer to the workbook you are editing.

What paste values only actually copies

Excel cells can contain constants, formulas, formatting, and other properties. A normal copy can transfer several of these together. The xlPasteValues option limits the paste to the cell values: typed entries such as numbers and text, as well as the current results calculated by formulas.

For example, if A2 contains =B2*C2 and currently displays 120, a values-only paste places 120 in the destination cell. It does not transfer the formula or its references. If the formula result is an Excel error such as #N/A, that error value is copied too; values-only does not repair or suppress errors.

“Values” does not mean “what the screen looks like.” A number formatted as currency is still a numeric value. The destination’s number format controls how the pasted number appears, so the same value might display as 120, $120.00, or a date depending on the destination formatting. Text and numbers are also distinct: a number stored as text remains text.

PasteSpecial versus direct range assignment

There are two useful approaches, and the right choice depends on whether you need Excel’s paste behavior or simply want to transfer results.

Use PasteSpecial when you want a familiar copy-and-paste operation, such as copying from one sheet to another while leaving the destination’s existing formatting in place. It uses Excel’s clipboard and supports paste options such as values, formats, or formulas.
Assign Value2 directly when you want a straightforward transfer between ranges and do not need clipboard behavior. It is often simpler for macros that move data between known ranges.

Neither method transfers source formulas when used for a values-only result. Both leave the source unchanged. With either method, check that the destination is the intended size and location; a macro can overwrite existing data without presenting the prompts a person might expect during manual editing.

Use PasteSpecial for a destination-sized range

If you want to specify only the top-left destination cell, use Resize to create a destination range matching the source dimensions. This avoids manually repeating the row and column counts in both addresses.

Sub CopyRangeValuesOnly()
    Dim sourceRange As Range
    Dim destinationTopLeft As Range
    Dim destinationRange As Range

    ' Save a workbook copy before running this macro.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:C20")
    Set destinationTopLeft = ThisWorkbook.Worksheets("Sheet2").Range("A2")
    Set destinationRange = destinationTopLeft.Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

The destination starts at A2 and expands to the source’s number of rows and columns. This is useful when the source address changes but you calculate or determine its size elsewhere in your macro. It does not make the destination safe from overwriting: confirm that the expanded range does not contain data you need to keep.

Copy values without using the clipboard

For ranges of the same dimensions, direct assignment can be even shorter. This example replaces formulas and existing entries in A2:C20 with their current results or constants. The source and destination are the same range, so formulas there become fixed results.

Sub ReplaceFormulasWithValues()
    Dim targetRange As Range

    ' Save a workbook copy before running this macro.
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:C20")

    ' Keep the current values and remove formulas from this range.
    targetRange.Value2 = targetRange.Value2
End Sub

For two different ranges, assign the source values to an equally sized destination, for example destinationRange.Value2 = sourceRange.Value2. The ranges should have the same number of rows and columns. This statement does not copy formatting, comments, validation rules, or column widths. It also does not put anything on the clipboard, so there is no need to clear Application.CutCopyMode.

Value2 reads and writes the underlying cell values without using VBA’s Date and Currency subtypes for dates and currency-formatted numbers. That makes it a useful choice for bulk value transfers. The displayed formatting remains a separate matter: direct assignment does not make the destination look like the source.

How to choose the right method

Use these practical questions to decide which pattern fits your task:

Are you copying between separate ranges and want paste-values behavior? Use Copy and PasteSpecial.
Are you replacing formulas in place? Assign the range’s Value2 to itself.
Are you transferring a block between known ranges? Direct assignment is usually concise, provided the ranges match in size.
Do you need to preserve destination formatting? Both values-only approaches leave source formatting behind; the destination’s existing formatting generally remains.
Do you need the source formatting too? Values-only is the wrong paste option. Choose a different paste option or copy the formatting separately.

Direct assignment can be preferable for many cells because it avoids a sequence of clipboard operations. However, it is not a universal replacement for copy and paste: it transfers cell values only, not every feature associated with copying cells. If you rely on special paste behavior, use PasteSpecial and specify the option deliberately.

Formatting, dates, and other details

A value-only paste normally keeps the destination cells’ existing formatting rather than applying the source’s number formats, fill colors, borders, or font styles. If the destination is blank and uses General format, a date serial may appear as a number. To display it as a date, format the destination cells separately. Similarly, copying a decimal into a cell with a percentage format can make it appear as a percentage without changing the underlying value.

There are a few other distinctions worth knowing. Formula results are captured as they exist when the macro runs, so a later change in a formula’s inputs will not update the pasted result. If Excel is set to manual calculation and formulas have not been recalculated, the stored result may not reflect recent input changes. Also, blank cells in a copied range can affect destination cells; do not assume a values-only paste means “skip blanks.”

Excel tables, merged cells, protected sheets, and filtered ranges can introduce extra constraints. A destination on a protected worksheet may reject the operation. Merged areas can make range dimensions or paste alignment incompatible. A normal copy of a range that includes hidden rows should not be treated as an instruction to copy only visible records. For filtered data, define the intended source cells explicitly and test the macro on a copy before applying it to important records.

Common problems and fixes

Run-time error during paste: Check that the destination is not protected, that merged cells are compatible, and that the source and destination dimensions make sense.
Values appear with the wrong format: The values may be correct while the destination number format differs. Apply the required number format separately.
Formulas remain in the destination: Confirm the code uses xlPasteValues, not a normal paste, or use direct Value2 assignment.
The wrong workbook or sheet is affected: Qualify worksheet references and confirm what ThisWorkbook refers to. A macro in another workbook may need a reference to the target workbook instead.
The macro cannot be undone: That is expected. VBA does not provide the usual Ctrl+Z recovery for changes made by a macro. Restore from a saved copy or another backup if necessary.

When debugging, place the cursor inside the procedure and run it one line at a time with F8 in the Visual Basic Editor. You can inspect the range addresses and dimensions before the paste occurs. This is especially helpful when the source range is determined dynamically, because an incorrect last row can quietly include blanks or miss records.

Manual values-only paste and platform notes

For a one-time operation, VBA may be unnecessary. In Excel for Microsoft 365 on Windows, copy the cells, then use Home > Paste > Values (the Values icon) to paste results without source formulas or formatting. The Paste Special dialog is another route: press Ctrl+Alt+V, choose Values, and confirm. On Mac, use the Home tab’s Paste menu and choose Values; keyboard shortcuts can differ by keyboard and Excel settings.

To open the VBA editor on Windows, press Alt+F11. On Mac, Option+F11 is commonly used, and some keyboards require Fn as well. Excel for the web can open and edit many workbooks, but it cannot run VBA macros; use the desktop Excel application to run this code. Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA in that file.

Frequently Asked Questions

How do I paste values only in VBA? Copy the source range, then call destinationRange.PasteSpecial Paste:=xlPasteValues. Clear copy mode afterward with Application.CutCopyMode = False if you used the clipboard.

How do I turn formulas into values in the same cells? Set a range’s Value2 property equal to itself, as in targetRange.Value2 = targetRange.Value2. This replaces formulas with their current results, so save a copy first if you may need the formulas later.

Does VBA paste values only keep the formatting? The values-only operation does not copy the source formatting. The destination generally retains its existing formatting, which determines how numbers and dates appear.

Can I paste values without copying first? Yes. Assign an equally sized source range’s Value2 to the destination range’s Value2. This transfers the values directly without using Excel’s clipboard.

Why can’t I undo a VBA values-only paste? Running a macro clears Excel’s normal undo history for the changes it makes. Save a separate workbook copy before running a macro that overwrites 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.