VBA Copy Range and Paste as Values in Excel

To copy an Excel range and paste only its values with VBA, copy the source range and use PasteSpecial with xlPasteValues. For example, after setting source and destination ranges, use source.Copy followed by destination.PasteSpecial Paste:=xlPasteValues; this replaces formulas with their current results without copying the source formatting.

What “paste as values” means

A cell can contain a constant, such as the number 25, or a formula, such as =A1+B1. The value shown by a formula is its calculated result. Pasting a range as values transfers those results and constants, rather than the source formulas. A destination cell that receives the result of =A1+B1 will contain a fixed number, not a formula that continues to recalculate from A1 and B1.

This is useful when you want a snapshot of a report, need to remove links to source data, or are preparing data for another process. The operation does not normally bring over the source cell’s number format, font, fill, borders, or validation. Existing destination formatting remains in place. That distinction matters: a date may arrive as a serial number, for example, and display as a number if the destination cell is formatted as General.

Basic VBA macro for copying values

This example copies A2:D20 from a worksheet named Data to A2 on a worksheet named Report in the same workbook. The destination range is set to the same size as the source, which makes the intended paste area explicit.

Sub CopyRangeAsValues()
    Dim source As Range
    Dim destination As Range

    ' Change the sheet names and range addresses as needed.
    Set source = ThisWorkbook.Worksheets("Data").Range("A2:D20")
    Set destination = ThisWorkbook.Worksheets("Report").Range("A2:D20")

    ' Copy the cells, then paste only their values.
    source.Copy
    destination.PasteSpecial Paste:=xlPasteValues

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

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the procedure into the module, and run it from the editor or from the Macro dialog. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA. On Mac, use Tools > Macro > Visual Basic Editor; keyboard shortcuts can vary with the Mac keyboard and system settings.

Before running any macro, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so checking the sheet names and destination address first can prevent accidental overwrites.

How Copy and PasteSpecial work

source.Copy places the selected range on Excel’s copy clipboard. The next line calls the Range method PasteSpecial on the destination and specifies the paste type. The constant xlPasteValues tells Excel to transfer values only. The order is important: the copy must happen before the paste, and the destination must refer to the cells that should receive the results.

Using fully qualified references, as in ThisWorkbook.Worksheets("Data"), avoids relying on whichever sheet happens to be active. ThisWorkbook means the workbook containing the VBA code. If the source is in another open workbook, refer to that workbook explicitly instead, and make sure it is open when the macro runs.

The sample uses equal-sized ranges because that is easy to verify and maintain. For a 19-row by 4-column source, the destination begins at A2 and covers 19 rows by 4 columns. If the source range changes size, update the destination range as well or calculate its dimensions in code. A mismatch can lead to confusing paste behavior, especially when the destination contains merged cells or other layout constraints.

Choosing between PasteSpecial and direct assignment

For a conventional copy-and-paste operation, Copy plus PasteSpecial is familiar and closely matches Excel’s Paste Values command. It is a suitable choice when the macro already needs the clipboard, or when you want to use Excel’s paste options.

If you only need to transfer cell contents within VBA, direct assignment is often shorter and avoids the clipboard:

Sub AssignRangeValues()
    Dim source As Range
    Dim destination As Range

    Set source = ThisWorkbook.Worksheets("Data").Range("A2:D20")
    Set destination = ThisWorkbook.Worksheets("Report").Range("A2:D20")

    ' Transfer the calculated or stored values without copying formats.
    destination.Value = source.Value
End Sub

For same-sized ranges, this assigns each source cell’s value to its matching destination cell. It does not copy formulas, formatting, comments, or column widths. The destination’s existing formatting is left alone. Direct assignment is particularly convenient for routine data movement because it does not leave Excel in copy mode.

Excel also exposes Value2. It avoids the Currency and Date subtypes used by the Value property; dates and currency amounts are passed as their underlying numeric values. That can be helpful in data-processing code, but the destination’s number format controls how those numbers appear. Choose Value or Value2 deliberately if your later VBA logic depends on how dates or currency are represented. Neither property copies the displayed text alone: a cell formatted to show 10.0% can hold the numeric value 0.1.

Examples for common tasks

To replace formulas with their current results in the same cells, copy the range and paste values back into that range. This is a destructive edit: after the paste, the formulas are gone. Save a backup or copy the original formulas elsewhere if you may need them later.

Sub FreezeFormulaResults()
    Dim target As Range

    Set target = ThisWorkbook.Worksheets("Report").Range("B2:F30")

    ' Replace formulas in this range with their current results.
    target.Copy
    target.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

To paste a smaller source range at a single top-left cell, set the destination to a same-sized range for clarity. For example, if the source is A1:C5, the destination could be E1:G5. This makes it obvious that 15 cells are being written and helps you avoid overwriting adjacent content unintentionally.

To copy between workbooks, use explicit workbook and worksheet references for both ranges. Confirm the target workbook is open and that the destination sheet is not protected against edits. If the workbook names or sheet names may change, add checks and clear error messages rather than assuming the references will always be valid.

Values, formulas, formatting, and displayed text

“Values” does not mean “what I can see on screen in every respect.” A formula’s result is transferred, but the source’s number format is not. A value displayed as $1,250.00 might be stored as the number 1250; a value displayed as 12% might be stored as 0.12. Pasting values copies the underlying result, while the destination’s formatting determines its appearance.

If you need both values and the source’s formatting, use two paste operations: one for values and one for formats. For example, set the same source and destination ranges, copy the source, paste with xlPasteValues, then copy the source again and paste with xlPasteFormats. The second operation applies the source formats but not its formulas. Check the result if the destination has special formatting you need to preserve, because applying formats will replace those destination formats.

If you need the exact characters displayed in a cell rather than its underlying number or formula result, that is a different requirement. The displayed text can depend on column width and number format, so copying values is generally more reliable for calculations and structured data. For a plain text export, decide whether you need the stored value, a formatted text representation, or a saved file format before writing the macro.

Common errors and how to avoid them

Subscript out of range: A worksheet name is misspelled or does not exist in the referenced workbook. Confirm the tab name, including spaces, and whether ThisWorkbook is the workbook you intend.
Wrong destination: An unqualified Range("A1") refers to the active sheet, which may not be the sheet you expect. Qualify ranges with a workbook and worksheet reference.
Unexpected overwrite: The destination range contains useful data. Review the target address and save a copy before running a macro; VBA changes cannot be reversed with Ctrl+Z.
Protected sheet or merged cells: Excel may refuse to write to protected cells or may not accept a paste that conflicts with merged-cell boundaries. Unprotect the sheet only when authorized, and use a destination layout compatible with the source.
Clipboard remains active: Excel may show a moving border after copying. Setting Application.CutCopyMode = False at the end clears copy mode; it is cleanup, not a substitute for a successful paste.

Filtered lists and hidden rows deserve special attention. A normal range reference identifies the cells in that range; it does not automatically express every possible intention about visible rows only. If the task is to copy visible cells from a filtered list, define and test that requirement separately, particularly when the selection contains multiple noncontiguous areas. Do not assume a macro that works on a plain rectangular block will behave identically on a filtered or irregular selection.

Speed, workbook safety, and practical habits

For a modest range, either method is usually straightforward. For a large data block, direct assignment can be more efficient because it avoids repeated clipboard operations. If a macro processes many separate ranges, consider whether the task can be expressed as one rectangular transfer, but do not combine areas if doing so would copy unwanted cells or change the data layout.

Before running a macro that overwrites cells, check the source and destination addresses, confirm the workbook and sheet names, and save a backup. Macros can make changes that are difficult to reconstruct, and Ctrl+Z does not undo VBA actions. Run code only from a workbook or source you trust; do not enable macros merely because a file asks you to. If the workbook is shared, make sure the destination is not being edited by another process or person at the same time.

When adapting code, keep the source and destination references visible near the top of the procedure. That makes the macro easier to audit than code that depends on active cells, selections, or whatever sheet was open when it started. For recurring reports, also consider adding a check that the destination has enough rows and columns and that the source is not empty before writing.

Frequently Asked Questions

How do I paste values in VBA without using the clipboard?
Set source and destination ranges of matching dimensions, then use destination.Value = source.Value. This transfers values without copying formulas or formatting and does not use Excel’s copy mode.

Does xlPasteValues copy formulas?
No. It pastes the current calculated result of a formula, not the formula itself. A formula that refers to changing cells becomes a fixed value at the destination.

Will paste values keep the destination formatting?
Yes, a values-only paste does not apply the source’s formatting. The destination’s existing number format, font, fill, and borders remain, unless another part of the macro changes them.

Can I paste values over the original formulas?
Yes. Copy the range and paste values back onto the same range. This replaces the formulas with their current results, so save a backup first if you might need the formulas again.

Why does a date look like a number after pasting?
The date may have been transferred as its underlying serial value while the destination retained General formatting. Apply an appropriate date number format to the destination if you want it to display as a date.

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.