VBA Code to Copy and Paste Values in Excel

To copy a range’s results without its formulas, use VBA’s PasteSpecial with xlPasteValues; for a fast transfer between same-sized ranges, assign one range’s .Value to the other. Both methods leave formulas behind and put their current results in the destination.

Two ways to copy values with VBA

The Paste Special method mirrors the Excel command Paste Values. It is a good choice when you want the macro to behave like a normal copy-and-paste operation, or when you may later change the paste type. The direct-assignment method is shorter and often faster for a straightforward range-to-range transfer.

Paste Special: Copy the source, then paste using xlPasteValues. The destination receives values, not formulas or source formatting.
Direct assignment: Set the destination range equal to the source range’s .Value. This transfers the cells’ values without using the clipboard.

For either method, specify the worksheet and range explicitly. Code that relies on whichever sheet happens to be active can copy the wrong cells when someone changes the selection or opens another sheet.

Paste Special example for fixed ranges

This example copies the results in A2:D20 on a worksheet named Input to the same-sized area on Output. Change the sheet names and addresses to fit your workbook. The worksheets must be in the workbook that contains this macro because the code uses ThisWorkbook.

Sub CopyValuesWithPasteSpecial()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' Set explicit worksheets in the workbook containing this code.
    Set sourceSheet = ThisWorkbook.Worksheets("Input")
    Set destinationSheet = ThisWorkbook.Worksheets("Output")

    ' Copy results only; source formatting and formulas are not pasted.
    sourceSheet.Range("A2:D20").Copy
    destinationSheet.Range("A2").PasteSpecial Paste:=xlPasteValues

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

The source and destination do not need to start at the same cell address. In this example, the source is A2:D20 and the top-left destination cell is A2, so the copied block fills A2:D20 on Output. If the destination starts at another cell, such as F5, the pasted block begins there and retains the source block’s dimensions.

Direct assignment without the clipboard

For a plain transfer of results, direct assignment is usually the simplest option. It does not display a moving border, and it avoids using Excel’s clipboard. The source and destination ranges must have matching row and column dimensions.

Sub CopyValuesByAssignment()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' Set explicit worksheets in the workbook containing this code.
    Set sourceSheet = ThisWorkbook.Worksheets("Input")
    Set destinationSheet = ThisWorkbook.Worksheets("Output")

    ' Transfer values only between equal-sized ranges.
    destinationSheet.Range("A2:D20").Value = _
        sourceSheet.Range("A2:D20").Value
End Sub

Direct assignment replaces the destination cells’ contents, but it does not copy their formatting, column widths, or other cell settings. Existing destination formatting remains. This can be useful when Output already has a designed layout and you only want to refresh its data.

Excel also offers the .Value2 property. It transfers the underlying values without the special Date and Currency subtypes that .Value can return in VBA. For most ordinary copy-values tasks, .Value is clear and sufficient; use .Value2 when your code needs to handle raw numeric values consistently. Excel’s date display still depends on the destination cell’s number format.

What “values only” means

A formula cell contains a formula and displays a calculated result. For instance, a cell containing =B2*C2 may display 48. A values-only paste puts the current result, 48, into the destination rather than the formula. If the source formula later recalculates to a different result, the pasted number does not update.

The same principle applies to other contents. A text formula that displays a name becomes its current text result; a formula that returns a date becomes its current date value. A formula error, such as #N/A, is copied as an error result, not repaired or converted into a valid value. A source cell that already contains a typed number or text remains that number or text.

Values-only paste is not the same as pasting a picture or copying a cell’s appearance. It does not carry over the source’s font, fill, borders, or number format. For example, a date serial pasted into a destination formatted as General may appear as a number. Apply the desired destination number format separately if the display matters.

Choosing the source and destination ranges

Fixed addresses such as A2:D20 are easy to read and dependable when the layout never changes. When rows are added regularly, a fixed range may omit new data or include blank rows. A macro can instead determine the last populated row, but the right approach depends on the data: a reliable key column is usually a better guide than a column that may contain blanks.

When the source and destination are on different worksheets, qualify both ranges with worksheet variables, as in the examples. When they are in different workbooks, qualify the workbook as well; otherwise, code can accidentally refer to a similarly named sheet in the wrong file. Remember that ThisWorkbook means the workbook where the macro code is stored, not necessarily the workbook currently active on screen. If a macro is stored in Personal.xlsb or an add-in, set explicit workbook variables for the files being processed.

For Excel tables, identify the intended table column or data body rather than assuming the table will always occupy the same worksheet rows. If you copy a full range containing headers, the headers are values too. Decide whether the destination should receive them before running the macro.

How to add and run the macro

Save a separate copy of the workbook before running unfamiliar or destructive code. VBA macros cannot be undone with Ctrl+Z.
In desktop Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module and paste the procedure into the module.
Replace the example worksheet names and range addresses with the ones in your workbook. Check spelling, including spaces, against the worksheet tabs.
Return to Excel and run the macro from the Visual Basic Editor, or use Developer > Macros, select the procedure, and choose Run. Save the file as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code.

On a Mac, VBA is available in desktop Excel, though keyboard shortcuts and some editor details can vary with the keyboard and Excel release. Use the Developer tab’s Visual Basic command if the familiar Windows shortcut is unavailable. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros; open the file in desktop Excel to use these procedures.

Common problems and how to avoid them

“Subscript out of range”: A worksheet name in the code does not exactly match a sheet in the workbook referred to by ThisWorkbook. Check the name and whether the code is stored in the intended file.
The wrong workbook changes: The code uses an unqualified range or assumes the active workbook is the intended one. Set worksheet and workbook variables explicitly.
Only part of the data is copied: The source address is fixed and no longer covers all populated rows. Revisit the range-selection logic before expanding the data.
The pasted result looks different: Values-only operations do not copy number formats. A date or percentage can display unexpectedly if the destination has General or an incompatible format. Apply an appropriate format separately.
The macro fails on a protected sheet: Sheet protection can prevent changes to locked destination cells. Unprotect the sheet through an authorized process or prepare a permitted destination before running the macro.
Excel reports a spill or merged-cell issue: A destination overlapping a dynamic-array spill area or incompatible merged cells may not accept the pasted range. Choose a clear, correctly sized destination.

A macro may also be blocked by security settings. Do not enable macros in a file simply because it asks you to; use code from a source you trust and follow your organization’s security rules. Values-only copying does not make a workbook safe, and a macro can change or delete data beyond the range you expect if its code is wrong.

Which method should you use?

Choose PasteSpecial when you want a recognizable paste operation or when the clipboard-style workflow suits a larger macro. Choose direct assignment when you only need to transfer the contents of equal-sized ranges and want concise code. Both can achieve the central goal: keeping the current results while removing the formulas from the destination.

If you need formulas to remain live, neither values-only method is appropriate; copy the formulas instead. If you need both the results and the source appearance, use a suitable combination of value transfer and formatting operations, or copy the whole range and then replace formulas with values. Keep these tasks separate in the code so it is clear which formatting and formulas should survive.

Frequently Asked Questions

What is the VBA code to paste values only?
Copy the source range, then use destinationRange.PasteSpecial Paste:=xlPasteValues. For example, the fixed-range macro above uses that command after copying A2:D20.

How do I copy values without using the clipboard?
Assign the source range’s value to an equally sized destination range, such as destinationRange.Value = sourceRange.Value. This transfers results without copying formatting.

Does Paste Values remove the original formulas?
No. The source formulas stay in place. The destination receives their current results as values, so it will not recalculate from the source formula.

Can I copy values between worksheets?
Yes. Set a worksheet variable for each sheet and qualify both ranges, as shown in the examples. For sheets in another workbook, identify that workbook explicitly too.

Can I undo a VBA values paste?
Usually not with Ctrl+Z. Excel clears its ordinary undo history when a macro runs, so save a backup or copy of the workbook before executing a macro that changes data.

Can VBA run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run VBA.

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.