Excel VBA Copy Paste Special: Values, Formats, and More

In Excel VBA, copy a range and call PasteSpecial on the destination, specifying what to paste. For example, use sourceRange.Copy followed by destinationRange.PasteSpecial Paste:=xlPasteValues to paste calculated values without formulas or source formatting.

What Copy and Paste Special Do in VBA

Excel’s ordinary copy operation can bring several things with it: formulas, displayed values, formatting, comments or notes, and other cell properties. Paste Special lets a macro choose which parts to transfer. That distinction matters when a report needs fixed results but should retain its own layout, or when a template needs formatting without overwriting its contents.

In VBA, the usual sequence is to copy a source range, then paste the chosen content into a destination range. The Paste argument takes an Excel constant, such as xlPasteValues or xlPasteFormats. The destination can be a single top-left cell; Excel pastes the copied range’s full shape starting there. It does not need to be selected first.

Paste Special is different from assigning one range’s value to another. A direct assignment transfers values without using the clipboard, while Paste Special exposes choices such as formats, formulas, and number formats. Select the method that matches the task rather than assuming that every kind of “paste” produces the same result.

Basic VBA Syntax

The core method has this form:

destinationRange.PasteSpecial Paste:=xlPasteValues

That line expects a range to have been copied first. A complete macro should qualify both ranges with their worksheets so it does not depend on whichever sheet happens to be active. For example, with a source in Data!A2:C20 and a destination beginning at Report!A2:

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

    ' Set the source and top-left destination cell.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:C20")
    Set destinationRange = ThisWorkbook.Worksheets("Report").Range("A2")

    ' Copy, then paste results without source formatting.
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValues

    ' Clear Excel's moving-copy outline.
    Application.CutCopyMode = False
End Sub

The destination cell anchors a three-column by nineteen-row paste, so the corresponding cells beginning at Report!A2 receive the copied results. Make sure the destination area is appropriate: existing contents in the pasted area can be replaced. The macro does not ask for confirmation before overwriting them.

Choose What to Paste

The Paste argument determines the content Excel transfers. Common choices include:

xlPasteAll pastes the copied cells’ contents and formatting, much like a normal paste.
xlPasteValues pastes results rather than formulas, without copying the source cell formatting.
xlPasteFormulas pastes formulas, without copying the source formatting.
xlPasteFormats pastes formatting without replacing the destination’s values or formulas.
xlPasteValuesAndNumberFormats pastes results together with number formats, useful when values such as dates or percentages should retain their display style.
xlPasteColumnWidths pastes the copied columns’ widths.

These are Excel VBA constants, not text labels: write them without quotation marks. For example, Paste:=xlPasteFormats is valid, while Paste:="xlPasteFormats" passes a string instead of the required constant.

For a report that needs values and the source’s number display but should keep its own font, fill, and borders, use xlPasteValuesAndNumberFormats. For values with all source formatting, paste values first and formats in a second operation. Think through which destination properties should change before choosing a constant.

Examples for Common Tasks

To paste formatting only, use the same copy-and-paste sequence but choose the formats constant. The source and destination should cover the same-sized area if the intent is to match corresponding cells:

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

    Set sourceRange = ThisWorkbook.Worksheets("Template").Range("A1:D10")
    Set destinationRange = ThisWorkbook.Worksheets("Report").Range("A1:D10")

    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

To paste values and then formats, perform two paste operations while the copied range remains available. This gives you the source results and appearance, but it also replaces destination formatting:

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

    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:C20")
    Set destinationRange = ThisWorkbook.Worksheets("Report").Range("A2")

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

For formulas rather than their current results, replace the first paste option with xlPasteFormulas. Relative references may adjust to the destination, just as they do when formulas are copied in the worksheet. If the destination is elsewhere, inspect formulas that refer to other cells before relying on the pasted results.

PasteSpecial Options and Useful Parameters

Besides Paste, the method accepts optional arguments for operations and special handling. One often-used pattern is:

destinationRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Operation:=xlNone means paste without applying an arithmetic operation to existing destination cells. Other operation constants can combine copied numbers with existing destination numbers, but such operations can change results and should be used deliberately. SkipBlanks controls whether blank cells in the copied range are skipped instead of pasted over destination cells. Transpose:=True switches rows and columns during the paste.

These parameters are optional; a simple values-only macro usually needs just the Paste argument. In particular, setting SkipBlanks:=True does not mean “remove blank cells.” It means blank source cells leave corresponding destination cells unchanged. That is useful for selectively filling a destination, but it can also leave old data behind if you expected a complete replacement.

PasteSpecial or Direct Value Assignment?

For a straightforward values-only transfer, direct assignment is often shorter and avoids the clipboard. With source and destination ranges of identical dimensions, this form copies the values:

destinationRange.Value = sourceRange.Value

For large ranges, assigning values directly is often a practical alternative to copying and pasting. It does not transfer formatting, and the destination must be sized to match the source for a predictable cell-by-cell result. A destination with an existing number format keeps that format, which may make a copied date serial or decimal display differently from the source.

Use Paste Special when you need a particular paste behavior, such as formats, formulas, number formats, transpose, or skipping blank source cells. Use direct assignment when the goal is simply to move cell values between equal-sized ranges. Be aware that Value can involve Excel’s value representation; for code that specifically needs the underlying value without VBA’s date and currency subtype behavior, developers may use Value2. Neither assignment method copies formulas as formulas unless you assign the formula property instead.

Common Problems and How to Avoid Them

“PasteSpecial method of Range class failed.” The clipboard may not contain a compatible copied range, or the target sheet may be protected. Check that Copy runs immediately before the paste and that the destination can be edited.
Wrong active sheet or workbook. Unqualified references such as Range("A1") depend on the active sheet. Qualifying each range with ThisWorkbook.Worksheets("SheetName") reduces accidental pastes into another workbook or sheet.
Destination data is overwritten. A paste beginning at one cell fills a whole block. Confirm the source dimensions and destination area before running a macro, especially when the source size can change.
Values look different after pasting. Values-only pasting does not copy source number formats. A date may appear as a serial number, or a decimal may not show as a percentage, if the destination format differs. Consider xlPasteValuesAndNumberFormats.
Unexpected formula references. Pasting formulas can adjust relative references. Use values-only pasting if the destination should contain fixed calculated results rather than live formulas.

Setting Application.CutCopyMode = False after a paste clears the moving border and ends copy mode. It is convenient cleanup, not a substitute for handling errors: if a macro stops before the cleanup line, Excel may remain in copy mode. For more robust automation, use error handling when a failure must also restore Excel’s state.

Running Macros Safely and Platform Notes

Before running a macro that writes to a workbook, save a separate copy. VBA actions generally cannot be undone with Ctrl+Z, so a mistaken paste may require restoring the saved version. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the macro.

On Excel for Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the procedure there. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. Run the procedure from the editor or the Macros dialog. Only enable macros in files you trust; macro security controls are designed to reduce the risk of running harmful code.

Excel for Mac desktop also supports VBA and Range.PasteSpecial, although keyboard shortcuts and some interface paths can differ by keyboard and Excel release. Excel for the web does not run VBA macros; use the desktop app to execute this code. Keep the ranges and workbook references explicit on either desktop platform, and test the procedure on a copy before applying it to important data.

Frequently Asked Questions

How do I paste values only in Excel VBA? Copy the source range, then call destinationRange.PasteSpecial Paste:=xlPasteValues. This pastes the cells’ results, not their formulas or source formatting. Clear copy mode afterward with Application.CutCopyMode = False if desired.

How do I paste formats only with VBA? Copy the source range and use destinationRange.PasteSpecial Paste:=xlPasteFormats. The destination’s values and formulas remain, while its formatting is replaced by the copied formatting.

Can I paste special without using Copy? The PasteSpecial method normally relies on a copied range and Excel’s copy state. For values alone, direct range assignment such as destinationRange.Value = sourceRange.Value avoids the clipboard, but it does not provide Paste Special’s formatting and transpose options.

How do I paste without overwriting destination blanks? If the source contains blank cells that should leave existing destination cells unchanged, use SkipBlanks:=True in PasteSpecial. This skips blank source cells; it does not delete cells or remove blanks from the destination.

Can Excel for the web run this VBA macro? No. VBA macros run in the Excel desktop application, including supported Windows and Mac versions, but not in Excel for the web. Open the workbook in desktop Excel to run the macro.

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.