Excel VBA: Copy and Paste Values Without Formulas

To copy a range’s results without its formulas in Excel VBA, copy the source range and use PasteSpecial with xlPasteValues. For example, sourceRange.Copy followed by destinationCell.PasteSpecial Paste:=xlPasteValues places values in the destination while leaving its existing formatting in place.

What “paste values” means in VBA

Excel cells can contain formulas, constants, and formatting. A formula such as =SUM(B2:B10) has a calculated result, but copying the cell normally transfers the formula as well. Pasting values transfers the result held by the cell instead, so the destination no longer depends on the original formula or its referenced cells.

In VBA, xlPasteValues is the Excel constant for the Values option in Paste Special. A typical operation has three parts: identify the source, copy it, and paste values at a destination. You do not need to select either range or display the Paste Special dialog.

The pasted result is not necessarily the text that you see on screen. Excel retains the underlying value: a number displayed with two decimal places can still have additional precision, and a date is stored as a number that Excel displays as a date when its number format calls for it. This distinction matters when the destination has a different format.

A complete VBA example

This example copies cells A2:C100 on a worksheet named Data to start at A2 on a worksheet named Archive. It pastes values only. Change the worksheet names and ranges to match your workbook.

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

    ' Set the source and the top-left destination cell.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:C100")
    Set destinationCell = ThisWorkbook.Worksheets("Archive").Range("A2")

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

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

The destination can be a single top-left cell: Excel fills a matching-size block from that point. In this example, the destination block is A2:C100 on Archive. The code assumes the named sheets exist in the workbook that contains the macro. If the macro is stored in another file, such as Personal.xlsb, ThisWorkbook refers to the file containing the macro, not automatically to the workbook currently selected.

How to run the macro safely

On Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. Return to Excel and run it from the Macros dialog, opened with Alt+F8. You can also assign a macro to a button. The workbook must be saved in a macro-enabled format, such as .xlsm, if it needs to retain VBA code.

Before running a macro that writes to a worksheet, save a separate copy of the workbook. VBA changes generally cannot be reversed with Ctrl+Z, and this procedure overwrites the destination values. On Mac, the Visual Basic Editor and macro commands are available in Excel, but keyboard shortcuts can differ by keyboard and Excel version; use the Developer tab or the Tools > Macro menu if the Windows shortcuts do not apply. Excel for the web can open a workbook containing macros, but it cannot run VBA; use the desktop Excel app.

For a simple one-off operation without code, select and copy the source, then choose Home > Paste > Paste Special > Values. On Windows, Ctrl+Alt+V opens the Paste Special dialog; choose Values and confirm. Ribbon labels and shortcut behavior can vary on Mac, so the menu path is a reliable alternative there.

Adapting the source and destination

For a range that changes size, identify its last row before copying rather than hard-coding the final row. For example, if column A has a header in A1 and no gaps in the records, Cells(Rows.Count, 1).End(xlUp).Row finds the last used row in column A. In a macro, qualify both Rows and Cells with the intended worksheet, or Excel may use the active sheet by mistake. Check that the result is greater than the header row before building a range, particularly when the sheet may contain no records.

Likewise, choose a destination that is large enough and does not overlap the source in a way that would disrupt the operation. A protected sheet, merged cells, or a destination inside an Excel table can affect what Excel permits or how the result behaves. If you intend to append records, calculate the next available row in the destination first and set the destination cell there; do not assume that pasting at A2 will preserve existing archive entries.

Alternative: assign values directly

For copying data within VBA, direct assignment can be simpler and faster because it does not use Excel’s clipboard. It also avoids leaving the moving copy border on screen. This version writes the source values into a destination range of the same dimensions:

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

    ' Set the source and a destination block of matching size.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:C100")
    Set destinationRange = ThisWorkbook.Worksheets("Archive").Range("A2").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Transfer values, not formulas or formatting.
    destinationRange.Value2 = sourceRange.Value2
End Sub

Use this approach for a single rectangular range when you only need cell values. The assignment leaves destination formatting untouched, just as a values-only paste does. It is not a general replacement for copying: it does not transfer cell formatting, comments or notes, data validation, column widths, or other cell features. Multi-area selections also need special handling; use a clearly defined rectangular source or process each area separately.

Value2 reads and writes the underlying values without returning VBA Date or Currency subtypes for corresponding Excel values. Excel dates and currency-looking numbers are still represented as numbers; the destination’s number format controls how those numbers appear. If matching the source display matters, copy the relevant number formats separately or use the values-and-number-formats paste option described below.

What is copied—and what stays behind

Values-only operations are useful for freezing a report, preparing a static export, or breaking links to source formulas. They do not make a visual duplicate of the source. After a values-only paste, these are the usual outcomes:

Formula cells become their current calculated results, not formulas that recalculate in the destination.
Constant numbers and text remain values. A text result that begins with zero can retain those zeros if it is genuinely stored as text.
Error results such as #N/A are copied as error values; pasting values does not repair the cause of an error.
Source font, fill, borders, and number formats are not transferred by xlPasteValues. Existing destination formats remain.
Cell comments or notes, data validation, and other non-value properties are not copied as part of a values-only paste.

Blank cells in the source can also leave corresponding destination cells blank, so do not treat a values-only copy as a way to update just the nonblank entries unless that is the behavior you intend. If the destination should retain its old value wherever the source is blank, add a condition to the macro and transfer only nonblank cells.

Choosing the right paste option

Choose the paste method according to what the destination needs, rather than assuming “values” means “everything visible.” The common choices are:

xlPasteValues: transfers values only and retains destination formatting.
xlPasteValuesAndNumberFormats: transfers values and number formats, but not every source formatting feature such as fonts and borders.
xlPasteFormulas: transfers formulas rather than freezing their current results.
xlPasteAll: copies a broader set of cell content and formatting, closer to a normal paste.

For a report where a date should still look like a date, but the destination has General formatting, xlPasteValuesAndNumberFormats may be more appropriate than values alone. If the destination is already formatted correctly, xlPasteValues is often the better choice. You can use the corresponding Excel constants as the argument to PasteSpecial.

Common problems and how to avoid them

A frequent problem is pasting into the wrong sheet. A reference such as Range("A2") without a worksheet qualifier points to the active sheet, which may change while a macro runs. Qualifying each range with ThisWorkbook.Worksheets("SheetName") makes the target explicit. Also confirm that the workbook reference is right: ThisWorkbook means the workbook where the VBA code is stored; ActiveWorkbook means the workbook currently active in Excel.

Another common surprise is that values look different after pasting. A date may appear as a large number if the destination is formatted as General, while a number may display rounded if the destination has fewer decimal places. The value may be intact even though its appearance changed. Compare the formula bar or inspect the destination number format before concluding that data was lost.

Because the copy-and-paste version uses Excel’s clipboard, another copy operation or an interrupted macro can affect the clipboard state. Clear it with Application.CutCopyMode = False when finished. If you need a dependable transfer of values only and the source is a contiguous range, direct Value2 assignment avoids this clipboard dependency. If formulas have not recalculated, the pasted result can reflect the last calculated result; calculate the relevant source cells first when fresh results are essential.

Finally, verify range sizes and sheet protection. A misspelled worksheet name produces a runtime error, while protected or merged destination cells can prevent a paste or make the result unsuitable. Test a macro on a copy with a small, representative range before using it on a large or important workbook.

Frequently Asked Questions

How do I paste values only in Excel VBA?
Copy the source range, then call PasteSpecial Paste:=xlPasteValues on the destination range. Set Application.CutCopyMode = False afterward if you want to clear Excel’s copy mode.

Does xlPasteValues copy the source formatting?
No. It transfers values, including formula results, but leaves the destination’s existing formatting in place. Use xlPasteValuesAndNumberFormats if the source number formats should also be transferred.

Can I copy values without using the clipboard?
Yes. For a single rectangular range, size the destination to match the source and assign destinationRange.Value2 = sourceRange.Value2. This transfers values without formulas or formatting.

Why did a date turn into a number after pasting values?
Excel stores dates as numbers. Values-only paste does not bring the source date format with it, so a destination using General formatting may display the underlying number. Apply a date number format or use the values-and-number-formats paste option.

Can I undo a VBA values-only paste with Ctrl+Z?
Usually not. Running a VBA macro clears Excel’s normal undo history, so save a separate workbook copy before running code that overwrites cells.

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.