VBA Copy and Paste Values Only in Excel

To copy a range in Excel VBA and paste only its values, use Copy followed by PasteSpecial Paste:=xlPasteValues. For example, sourceRange.Copy and then destinationRange.PasteSpecial Paste:=xlPasteValues transfers formula results and constants without copying the source formatting or formulas.

The basic VBA method

The usual approach is to identify the source and destination ranges explicitly, copy the source, and paste its values into the destination. This example copies a block of cells between two worksheets in the same workbook:

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

    ' Set matching source and destination blocks.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:C20")
    Set destinationRange = ThisWorkbook.Worksheets("Archive").Range("A2:C20")

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

Before running a macro, save a copy of the workbook: actions performed by VBA cannot be undone with Ctrl+Z. In the example, both ranges cover 19 rows and three columns, so each source cell has a corresponding destination cell. Change the worksheet names and addresses to match your file. The macro uses ThisWorkbook, meaning the workbook containing the macro, rather than whichever workbook happens to be active.

xlPasteValues is an Excel VBA constant for the values-only paste option. The final line clears Excel’s copy marquee and clipboard mode; it does not reverse the paste. It is usually a good cleanup step, especially if the macro continues with other operations.

What values-only paste transfers

A cell containing a formula such as =SUM(B2:B8) is pasted as its current calculated result, not as that formula. A cell containing a typed number or text remains that constant. If the formula currently evaluates to an error such as #N/A, the error value is transferred rather than the formula that produced it.

Values-only paste does not bring over the source cell’s number format, font, fill, borders, conditional formatting, or data validation. The destination keeps its existing formatting and other cell settings. That distinction can change what a pasted value looks like: a date result pasted into a cell formatted as General, for example, may appear as a serial number rather than as a calendar date. Apply the needed destination number format separately if the display matters.

“Values” also does not mean “the text exactly as displayed.” A cell formatted to show a number with two decimal places may hold a more precise number underneath. Pasting its value transfers the underlying value, while the destination format determines how it is shown. Likewise, displayed currency symbols and thousands separators generally come from formatting, not from the numeric value itself.

Build a safer, repeatable macro

For a routine task, hard-coded worksheet and range references are often more dependable than relying on the active cell or current selection. The sample above makes its scope visible: it always copies the same block from Data to Archive. If a worksheet name is misspelled or does not exist, VBA stops with an error instead of silently choosing another sheet.

For workbooks where the copied block changes size, determine the last row or source range deliberately before assigning it. Avoid using an entire worksheet as the source unless that is truly intended: Excel ranges can be extremely large, and copying more cells than needed can slow a macro or overwrite useful destination data. Before running a macro that writes to a destination, confirm the workbook, sheet, and address it will affect.

If source and destination are in different open workbooks, qualify each range with the correct workbook object rather than depending on the active workbook. You may need to open both workbooks first. Be especially careful with external files: a macro should not assume the destination workbook is open or that its sheet layout has stayed unchanged.

Copy and PasteSpecial versus direct assignment

When the goal is only to transfer cell contents as values, direct assignment is another option. It does not use the clipboard, so there is no copy mode to clear, and it is often convenient in a larger macro:

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

    ' Use blocks with matching row and column counts.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:C20")
    Set destinationRange = ThisWorkbook.Worksheets("Archive").Range("A2:C20")

    destinationRange.Value = sourceRange.Value
End Sub

With equal-sized ranges, assigning .Value transfers the source cells’ values to the destination and does not transfer their formulas or formatting. It is suitable for many straightforward, same-workbook transfers. The destination still keeps its own number formats, so the same date and currency display considerations apply.

Use Copy and PasteSpecial when you specifically want Excel’s paste operation, when you may extend the macro to paste other attributes, or when you find the explicit paste behavior easier to inspect. Use direct assignment when you only need values and want to avoid the clipboard. Do not assume either method will resize the destination safely for you; set the destination to the intended dimensions. For overlapping source and destination blocks, plan the operation carefully, because writing one block over another can destroy data needed later in the process.

Some macros use .Value2 rather than .Value for direct transfers. Value2 avoids converting certain values to VBA Currency or Date subtypes; dates are represented by their underlying serial numbers. This can be useful in data-processing code, but it does not preserve a date’s display format. Choose it with an understanding of how the receiving code will interpret the values.

Choose the right method for the task

For a single, fixed copy from one worksheet to another, the basic PasteSpecial macro is easy to read and leaves the source untouched. For repeated processing of many cells where no formatting is needed, direct assignment can be simpler and avoids clipboard operations. If you need both the values and the source number formats, values-only is not enough: use an appropriate additional paste operation or set the destination format separately.

For example, a monthly report might calculate totals on a Calculations sheet and then archive a snapshot on a Values Archive sheet. Pasting values prevents the archive from changing when the calculation formulas or source data later change. If readers must see those totals as currency, format the archive cells as currency as a separate, intentional step. Keeping the data transfer and display formatting separate makes the macro’s behavior clearer.

For a one-off action, you may not need VBA. In Excel for Windows, copy the cells, press Ctrl+Alt+V to open Paste Special, choose Values, and confirm. You can also use Home > Paste > Values. This manual method is useful when you want to inspect the selected destination before committing. A macro makes more sense when the operation is repeated, must follow a fixed process, or is part of a larger workflow.

Common problems and how to avoid them

Wrong active sheet or workbook: Unqualified references such as Range("A1") can refer to the active sheet. Qualify ranges with a worksheet and workbook, as in the examples, so the macro has an explicit target.
Destination is the wrong size: Make source and destination blocks the same shape for predictable cell-to-cell transfer. Check row and column counts before expanding a macro to variable-sized data.
Unexpected number display: Values-only does not copy number formats. If a date becomes a serial number or a decimal looks different, inspect the destination cell’s format before assuming the value itself was corrupted.
Results seem out of date: A formula’s result depends on calculation. If Excel is set to manual calculation, a formula result may not reflect the latest inputs until recalculation occurs. Decide whether the macro should recalculate the workbook or relevant formulas before it copies results.
Macro stops at PasteSpecial: Check that the source and destination references point to valid ranges and that the relevant workbook and sheets are available. A protected destination or an incompatible selection can also prevent a write; review protection and the exact error message.
Undo is unavailable: VBA changes generally clear Excel’s ordinary undo history. Save a separate copy before running a macro that overwrites cells, and test it on sample data first.

Also consider merged cells and filtered data before automating a transfer. Merged layouts can make range dimensions and paste behavior harder to reason about. If only visible rows should be copied from a filtered list, confirm the macro is targeting those visible cells rather than the full underlying range; a plain rectangular range reference does not by itself express every user’s intended filtering rule.

Excel versions, platforms, and macro safety

This guidance is for desktop Excel for Microsoft 365 on Windows. VBA macros cannot be created or run in Excel for the web, so open the workbook in the desktop app to use this code. Excel for Mac supports VBA, though some interface details and parts of the VBA environment differ; the basic range-copy approach is intended for desktop Excel, but a macro that relies on other Windows-specific features may need changes.

To keep VBA in a workbook, save it in a macro-enabled format such as .xlsm. Excel may display a security warning when opening a file with macros. Enable macros only for files and code you trust; do not treat a macro from an unknown source as safe just because it performs a familiar copy operation. If the Developer tab is hidden in Windows Excel, enable it through File > Options > Customize Ribbon, then select Developer. You can open the Visual Basic Editor from Developer > Visual Basic.

Frequently Asked Questions

How do I paste values only in VBA? Copy the source range and call destinationRange.PasteSpecial Paste:=xlPasteValues. Use explicit worksheet references and make the destination cover the intended cells.

Does PasteSpecial values keep the source formatting? No. It transfers values, not source formatting. The destination’s existing number format, font, fill, and borders remain in place.

Can I paste formula results without using the clipboard? Yes. For matching ranges, assign the source values directly to the destination, such as destinationRange.Value = sourceRange.Value. This transfers results rather than formulas and does not copy formatting.

Can VBA paste values between workbooks? Yes, if the macro references the correct source and destination workbooks and worksheets. Make sure the destination workbook is available and that the target range is the intended size.

Can I undo a values-only VBA paste with Ctrl+Z? Usually not. Running VBA generally clears Excel’s normal undo history, so save a separate workbook copy and test the macro before using it on important 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.