Copy Paste Values in Excel VBA: Methods and Examples

To copy a range’s results—not its formulas—in Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteValues. For a quick copy between equally sized ranges, direct assignment such as destination.Value2 = source.Value2 is often simpler and avoids the clipboard.

What “paste values” means in VBA

Excel cells can contain formulas, constants, formatting, and other attributes. When you copy a formula cell normally, Excel usually copies the formula and adjusts relative references for its new location. Pasting values instead writes the formula’s current calculated result into the destination. The destination no longer contains that formula, so it will not update when the inputs change.

For example, if A2 contains =B2*C2 and currently displays 24, a values-only paste puts the number 24 in the target cell. It does not put the formula there. This is useful for creating a fixed report, removing links to a source workbook, or replacing formulas with a snapshot of their results.

Values-only does not mean “copy every visible feature.” Standard values-only paste leaves the destination’s existing font, fill, borders, and number format in place. The pasted result can therefore look different from the source, even though the underlying result is the same.

Use PasteSpecial for a familiar copy-and-paste macro

Range.Copy and Range.PasteSpecial use Excel’s copy-and-paste model. This is a good choice when you want the behavior to resemble selecting a range, copying it, and choosing Paste Values from the ribbon. The example copies A2:D20 to F2:I20 on a worksheet named Report:

Sub PasteRangeValues()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' Use a single destination cell; Excel fills the matching-size range.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set sourceRange = ws.Range("A2:D20")
    Set destinationCell = ws.Range("F2")

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

Replace the sheet name and addresses with those in your workbook. The destination begins at F2 and receives the copied range’s 19 rows and four columns. Using a single starting cell makes the intended destination size clear; it also avoids relying on Excel to reconcile two differently sized ranges. Application.CutCopyMode = False ends the animated copy border and clears Excel’s copy mode after the paste.

The constant xlPasteValues specifies values-only paste. If you also want the source’s number formats—for example, to retain currency or date display—use xlPasteValuesAndNumberFormats instead. That still does not copy the source’s fonts, fills, or borders.

Use direct assignment to skip the clipboard

When source and destination ranges have the same number of rows and columns, direct assignment is usually the leanest method. It transfers the cells’ evaluated contents without calling Copy or PasteSpecial:

Sub AssignRangeValues()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    ' Source and destination have identical dimensions.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set sourceRange = ws.Range("A2:D20")
    Set destinationRange = ws.Range("F2:I20")

    destinationRange.Value2 = sourceRange.Value2
End Sub

This method does not use the clipboard, so there is no copy mode to clear. It is convenient for larger transfers and is less dependent on Excel’s interactive copy state. The source and destination should be the same size. If they are not, VBA may return an error or the assignment may not produce the layout you intended; set both ranges deliberately rather than assuming Excel will resize them.

Direct assignment transfers values, including text, numbers, logical values, and cell errors. It does not copy formatting, formulas as formulas, comments, validation rules, or column widths. In this example, formulas in the source are evaluated and their results are assigned to the target. Use .Formula or a copy operation without values-only paste when the goal is to transfer formulas instead.

Choose the method that fits the job

Both methods can produce a values-only result, but they suit different situations. Decide whether you need Excel’s paste behavior or only the cell contents:

Choose PasteSpecial when you want a familiar copy-and-paste workflow, need a paste type such as values plus number formats, or are adapting a macro that already copies ranges.
Choose direct assignment when the ranges are the same size and you want a straightforward transfer without the clipboard.
Choose a formula or another calculation when the destination should continue updating rather than becoming a fixed snapshot.
Choose a different paste type when you need formats, column widths, or other attributes in addition to values. Values-only paste deliberately excludes those.

For a one-off operation, you may not need VBA at all. In Excel for Microsoft 365 on Windows, copy the cells, select the destination, then use Home > Paste > Paste Values. The Windows shortcut for copying is Ctrl+C; Excel’s Paste Values shortcut can vary with the available ribbon key tips and keyboard configuration. On Mac, use Command+C to copy, then choose the values option from Home > Paste. A macro is more useful when the same operation must be repeated, applied to many ranges, or included in a larger process.

Copy values between worksheets or workbooks

Direct assignment works across worksheets as long as both ranges are qualified with their worksheet objects. This avoids accidentally using whichever sheet happens to be active. For example, you can assign from a Data sheet to a Report sheet by setting each range explicitly. With PasteSpecial, copy the source range and paste to a destination range on the other sheet; qualifying both objects also makes the code safer than relying on selection.

For another open workbook, use a workbook variable and refer to the workbook and worksheet by name. The source workbook must be open for a normal range-to-range assignment. If the source file is closed, a macro needs to open it or use another suitable data-import approach. Be cautious with workbook names: two open files can have similar names, and using ActiveWorkbook can direct a macro to the wrong file if focus changes. ThisWorkbook means the workbook containing the VBA code, which may not be the same as the workbook the user is currently viewing.

When pasting into a different workbook with PasteSpecial, the clipboard-based approach can be sensitive to workbook or application state. For a simple same-size transfer, direct assignment between fully qualified ranges is often easier to reason about. If the source formulas refer to external cells, assignment writes their current results, not a continuing connection to those formulas.

Understand dates, numbers, and formatting

Excel stores dates and times as numeric serial values and applies a number format to display them as dates or times. A values-only paste normally leaves the destination’s existing format untouched. If the destination is formatted as General, a copied date may appear as a number; if it is formatted as a date, the same underlying value may display as a date. That is a display-format issue, not necessarily a failed transfer.

The Value2 property is commonly used for direct range assignment because it reads and writes underlying values without converting currency and date values to VBA Currency or Date data types. The worksheet’s number format still controls how a value appears. If matching the source’s date or currency appearance matters, use PasteSpecial with xlPasteValuesAndNumberFormats, or set the destination’s number format separately.

Also consider what formulas currently return. A formula returning an empty string, such as ="", is not the same thing as a truly empty cell; copying its result may leave an empty-string value in the destination. Formula errors such as #N/A are results too, so values-only paste preserves the error rather than fixing it. Check the source calculations before converting formulas to fixed results.

Common problems and how to avoid them

Subscript out of range: The worksheet name in Worksheets("Report") does not exactly match a sheet tab, or the code is looking in the wrong workbook. Confirm the spelling and qualify the workbook.
Unexpected formatting: Values-only paste does not bring across the source’s number format, font, or fill. Use values plus number formats if that is what the task requires.
Wrong range size: With direct assignment, make source and destination dimensions match. With PasteSpecial, specify the destination’s top-left cell and verify that the receiving area is clear and large enough.
Values seem stale: A formula result may not have recalculated before the macro runs, depending on calculation settings and workbook state. Check Excel’s calculation mode and make sure the formulas have updated before taking a snapshot.
Hidden or filtered rows: A normal range reference includes hidden rows. If the task is intended to operate only on visible cells, define and handle that selection explicitly; filtered ranges can contain multiple separate areas and need careful testing.
Macro does not run: Save the file in a macro-enabled format such as .xlsm and use Excel’s macro security settings appropriately. Do not enable macros in an untrusted workbook just to run a copy operation.

Performance and macro safety

For a small range, either method is generally easy to use. For a large, same-size transfer, direct assignment is often efficient because it avoids clipboard interaction. If a macro processes many separate ranges, avoid activating sheets and selecting cells repeatedly; explicit references are clearer and reduce reliance on the user interface. For very large jobs, measure the complete macro in the actual workbook, since formulas, recalculation, and workbook structure can affect run time.

Before running a macro that replaces formulas, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a mistaken destination or range can be difficult to reverse. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, Option+F11 generally opens it, though some keyboards require the Fn key as well. In the editor, use Insert > Module to add a standard module for a macro like the examples above. Keep a backup, confirm the sheet and range names, and test on a small sample before applying the macro to important data.

Frequently Asked Questions

How do I paste values in Excel VBA without using the clipboard?
For equal-sized ranges, assign the source’s Value2 property to the destination’s Value2 property. It transfers the results without Copy, PasteSpecial, or clipboard state.

Does xlPasteValues copy formatting?
No. xlPasteValues pastes the cell results but leaves the destination formatting in place. Use xlPasteValuesAndNumberFormats if the source number formats should come along too.

Will pasting values keep formulas updating?
No. It replaces formulas with their current results. Use a formula-based link or copy the formulas if the destination must recalculate when inputs change.

Can I paste values from one sheet to another?
Yes. Refer to each sheet explicitly, then use PasteSpecial or assign equally sized ranges directly. Explicit references prevent the macro from depending on the active sheet.

Why does a pasted date show as a number?
The value may have copied correctly while the destination retained a General number format. Apply a date number format, or use values-and-number-formats paste when you want the source display format as well.

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.