Excel VBA Paste Values: Methods, Code, and Common Fixes

To paste values with Excel VBA, copy a source range and use PasteSpecial Paste:=xlPasteValues, or assign the source range’s Value2 property directly to an equally sized destination range. Paste Special uses Excel’s copy-and-paste operation; direct assignment transfers cell contents without using the clipboard and usually makes a simpler macro.

What “paste values” means in Excel

A worksheet cell can contain a constant, such as a number or text, or a formula. When a formula is in the source, its displayed result is not the formula itself. Pasting values transfers the formula’s current result as a constant, so the destination no longer recalculates from that formula. If the source contains constants already, those values are transferred as values.

Values-only pasting is useful when you need to freeze a report, remove formulas before sending a workbook to someone, or copy calculated results into a different part of a sheet without bringing along the original formulas. It is not the same as copying the entire cell: normal copying can also transfer formulas, formatting, comments or notes, and other cell attributes, depending on the paste operation.

One detail can be surprising: a date or percentage may look the same after a values-only paste because Excel stores dates and percentages as numbers and displays them using number formats. Paste Values does not, by itself, copy the source’s number format. If the destination has a different format, the same underlying value may appear differently.

Use PasteSpecial in a VBA macro

Use Range.PasteSpecial with the xlPasteValues option when you want VBA to perform the familiar copy-then-paste-values operation. In this example, the source and destination ranges have the same shape: 19 rows by 4 columns. Change the worksheet name and addresses to match your workbook.

Sub PasteValuesOnly()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Set explicit worksheet and range references.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("A2:D20")
    Set targetRange = ws.Range("F2:I20")

    ' Copy cell contents, then paste values only.
    sourceRange.Copy
    targetRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Before running any macro that changes a workbook, save a copy: VBA actions cannot be undone with Ctrl+Z. This macro expects a sheet named Data and a destination that is large enough for the copied range. It pastes values into F2:I20 while leaving the destination’s existing formatting in place. The source is not cleared.

The Application.CutCopyMode = False line clears Excel’s copy marquee and ends copy mode. It is not required to transfer the values, but it leaves the workbook in a cleaner state. If the macro stops with an error before reaching that line, Excel may remain in copy mode; you can press Esc or clear the copy mode in VBA.

Assign Value2 directly instead

For a straightforward transfer within a workbook, direct assignment is often faster and easier to maintain. It does not select cells, use the clipboard, or call Paste Special. With the same 19-row-by-4-column layout as above, this macro writes the source values into the destination:

Sub AssignValuesOnly()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Set explicit worksheet and range references.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("A2:D20")
    Set targetRange = ws.Range("F2:I20")

    ' Transfer values without formulas or clipboard use.
    targetRange.Value2 = sourceRange.Value2
End Sub

For an ordinary rectangular range, the source and target should have matching row and column counts. A mismatched destination can produce an error or an unintended result, so define both ranges explicitly rather than relying on whichever cell happens to be active. The assignment changes the destination’s contents but does not transfer the source’s formatting.

Value2 avoids the Currency and Date data-type conversions associated with the Value property. Excel dates are stored as serial numbers, so direct assignment transfers the underlying number; the destination’s number format determines whether it is displayed as a date, a general number, or something else. If matching the source’s visual appearance matters, copy the format separately or use Paste Special options for both values and formats.

Choosing the right method

Both methods can leave the destination with values rather than formulas, but they suit different tasks:

Use direct Value2 assignment for a simple range-to-range transfer, especially when you want to avoid the clipboard and keep the code concise.
Use PasteSpecial when the macro already copies a range or when you want to use Excel’s paste options, such as values, formats, or column widths.
Use a manual Paste Values command for a one-off task that does not need automation.
Use a formula or another data method if the destination should continue updating when the source changes. Pasting values deliberately removes that live link.

There is no general requirement to use Select or Activate before either VBA method. Explicit references such as ws.Range("A2:D20") make the target clear and reduce errors caused by a different workbook or sheet being active. Selecting cells is sometimes needed for user-facing navigation, but it is unnecessary for these transfers.

Paste values within one sheet or between sheets

The examples use one worksheet, but the source and destination can be on different sheets. Set each range against its own worksheet, and make sure both worksheet references point to the intended workbook. For example, a transfer from a sheet named Calculations to a sheet named Archive can use direct assignment like this:

Sub ArchiveValues()
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Use sheets in the workbook containing this macro.
    Set sourceRange = ThisWorkbook.Worksheets("Calculations").Range("B2:E50")
    Set targetRange = ThisWorkbook.Worksheets("Archive").Range("A2:D50")

    ' Keep the results, not the formulas.
    targetRange.Value2 = sourceRange.Value2
End Sub

Here, the 49-by-4 source range fills the 49-by-4 destination range. Using ThisWorkbook refers to the workbook that contains the VBA code, which is helpful when another workbook might be active. If the macro is stored in Personal.xlsb or an add-in, ThisWorkbook may not be the workbook containing the data; in that case, explicitly identify the intended workbook rather than assuming the active workbook is correct.

When copying between separate workbooks with Paste Special, both workbooks generally need to be open, and the copy operation uses Excel’s clipboard. Direct assignment can also transfer values between open workbook ranges if both are explicitly referenced. For unattended or large-scale imports, confirm that the intended destination workbook is open and that the macro’s references cannot resolve to a different file with a similar name.

Common problems and how to fix them

The destination contains formulas. Check that the code uses xlPasteValues, not a regular paste, or that it assigns Value2 rather than Formula. A normal copy can preserve formulas.
The pasted date or number looks wrong. The value may have transferred correctly while the destination format differs. Apply the desired number format separately, or use a paste option that also transfers formats.
The macro says a worksheet name is invalid. The tab name must match the worksheet reference exactly, including spaces. The VBA code name shown in the editor and the visible tab name are not always the same thing.
Only part of the range is filled. Check that the source and target addresses cover the same number of rows and columns, and that the target is not a merged-cell area that conflicts with the source shape.
The wrong sheet or workbook changes. Avoid unqualified references such as Range("A1"). Qualify the range with a worksheet and, where appropriate, a workbook reference.
Paste Special fails unexpectedly. Confirm that the source has been copied immediately before calling PasteSpecial, and that the target is a valid range. Direct assignment avoids clipboard state and can be a better choice for a simple transfer.

Another common misconception is that Paste Values makes a permanent snapshot immune to every later change. It removes the formula connection, but the destination can still be edited, cleared, or overwritten by another macro. If a record must be preserved, consider saving a dated copy or using a controlled archive process as well.

Run the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module through Insert > Module, and place the code there. Run a procedure from the editor or from Excel’s Macro dialog. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); saving it as .xlsx removes the VBA project.

Macros may be blocked by Trust Center settings or by an organization’s security policy. Only enable macros in files from sources you trust; do not lower security settings indiscriminately to run an unfamiliar workbook. Save a backup copy before testing, check the source and destination addresses, and run the macro on a small sample first when the workbook contains important data.

On Excel for Mac, the VBA editor and these basic range operations are available, although menus and keyboard shortcuts can differ from Windows. Use the Mac interface’s Developer tools or menu commands to open the editor if a Windows shortcut does not apply. Excel for the web can display and edit many workbooks, but it cannot create, edit, or run VBA macros; use desktop Excel for the VBA steps. A manual values-only paste remains an option in Excel for the web.

Manual alternatives and keyboard shortcuts

For a one-time paste in Windows desktop Excel, copy the cells with Ctrl+C, open Paste Special with Ctrl+Alt+V, choose Values, and confirm. You can also use Home > Paste > Paste Special and select Values in the dialog. The exact dialog layout can vary slightly by Excel build, but the Values option is the relevant choice. This method is useful when you do not need a reusable macro.

On Mac, use Command+C to copy, then open the Paste Special controls from the Home tab and choose Values; Windows’ Ctrl+Alt+V shortcut should not be assumed to work on Mac. In Excel for the web, use the available Paste Values choice from the paste menu after copying, if offered in the current interface. These manual commands affect the current selection, so check that the correct destination cell or range is selected before confirming.

Frequently Asked Questions

How do I paste values only in Excel VBA?
Copy the source range and call targetRange.PasteSpecial Paste:=xlPasteValues. For a simple rectangular transfer, you can instead assign targetRange.Value2 = sourceRange.Value2.

Does VBA Paste Values remove formulas?
Yes. If a source cell contains a formula, values-only paste places its current result in the destination as a constant. The destination will not update when the original formula’s inputs change.

Does PasteSpecial copy formatting too?
No. The xlPasteValues option transfers values without the source’s formatting. The destination keeps its existing formatting unless you separately paste formats or choose a different paste option.

Can I paste values without using the clipboard?
Yes. Assign the source range’s Value2 property to the destination range’s Value2 property. This is a direct transfer and is often the simplest approach when both ranges are rectangular and the same size.

Can I run an Excel VBA paste-values macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro, or use the web app’s manual Paste Values option for a one-off task.

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.