VBA Clear, Copy, and Paste: Excel Macro Examples

In Excel VBA, use Range.ClearContents to remove values and formulas while keeping formatting, Range.Clear to clear a range more completely, and Range.Copy with a destination or PasteSpecial to copy data. The right method depends on whether you want to preserve formatting, formulas, or the clipboard contents.

What “clear, copy, and paste” means in VBA

Excel’s ribbon commands have VBA equivalents, but the methods are more specific than the everyday words “clear” and “paste.” A range is a cell or group of cells, such as Range("A1:C10"). In a macro, you identify that range and tell Excel exactly what to do with it.

For clearing, the main choices are ClearContents, ClearFormats, and Clear. For copying, Copy can place a copy directly at a destination or put it on the clipboard for a later paste. For pasting only part of what was copied, use PasteSpecial, for example to transfer values without formulas or formatting.

These distinctions matter in recurring tasks. A data-entry form may need its old entries removed but its borders and number formats preserved. A report archive may need values pasted without formulas that refer back to the live workbook. A template reset may need both values and formatting removed.

Choose the right clear method

ClearContents removes cell values and formulas but leaves the cells’ formatting in place. This is usually the right choice for emptying an input area while keeping its layout.
ClearFormats removes formatting while leaving values and formulas. Use it when the data should remain but cell appearance needs resetting.
Clear clears the range more broadly, including its contents and formatting. Because it can also affect other cell-level settings, use it only when you intend a thorough reset.

For example, clearing B2:F50 with ClearContents does not remove the background colors, borders, or number formats in those cells. That can be useful when the same area is reused each week. By contrast, using Clear on that range is not just a quick way to delete its text: it may remove the presentation choices that make the sheet readable.

Clearing cells is also different from deleting cells. A clear operation leaves the cells in place, so other cells do not shift. The Delete command in Excel can remove cells and shift surrounding cells, changing the layout and potentially affecting formulas. If your goal is simply to empty a range, a clear method is generally the more predictable option.

Copy a range directly to a destination

When the source and destination are known, use the Destination argument. This copies the range without requiring a separate paste line or leaving Excel in copy mode. The following example copies cells A1:D10 from a worksheet named Report to Archive, beginning at A1:

Sub CopyReportBlock()
    Dim source As Range
    Dim target As Range

    ' Save a separate copy of the workbook before running this macro.
    Set source = ThisWorkbook.Worksheets("Report").Range("A1:D10")
    Set target = ThisWorkbook.Worksheets("Archive").Range("A1")

    source.Copy Destination:=target
End Sub

The destination can be a single starting cell when the copied range is larger than one cell; Excel places the copied block from that starting point. The source and destination sheets must exist with those names, or VBA will stop with an error. If a workbook has several sheets with similar names, check spelling and spaces in the tab names.

This method copies formulas, values, and formatting as a normal copy would. It is not the same as assigning values directly. If you need only the displayed results and not the formulas behind them, use a values-only approach described below.

Paste values, formulas, or formats only

PasteSpecial is useful when a normal copy would bring along more than you want. For example, a copied formula such as =A2*B2 may change its cell references when pasted elsewhere. Pasting values instead stores the calculated result, so the destination no longer depends on the source formula.

The example below copies the values and formulas in A2:C20 on the Input sheet, then pastes the calculated values into A2 on the Results sheet. Save a separate copy of the workbook before running it; macros cannot be undone with Ctrl+Z.

Sub PasteValuesOnly()
    Dim source As Range
    Dim target As Range

    ' Save a separate copy of the workbook before running this macro.
    Set source = ThisWorkbook.Worksheets("Input").Range("A2:C20")
    Set target = ThisWorkbook.Worksheets("Results").Range("A2")

    source.Copy
    target.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Other useful paste types include xlPasteFormats for formatting and xlPasteFormulas for formulas. xlPasteValuesAndNumberFormats transfers results along with number formats, which can help retain currency, date, or percentage displays without transferring formulas. Choose deliberately: values-only pasting does not preserve the original formulas, while formats-only pasting does not transfer the underlying data.

A direct assignment can be simpler when you want values and the source and destination ranges have matching dimensions. For example, assigning one range’s Value to another range’s Value avoids the clipboard. It does not copy formatting, and it transfers results rather than formulas. For a beginner, Copy followed by PasteSpecial is often easier to understand when the task specifically calls for a paste operation.

Clear cells with a small, targeted macro

For a reusable input form, a narrowly targeted macro is safer than clearing an entire worksheet. This example removes entries from B2:F50 on the Input sheet but keeps the existing cell formatting:

Sub ClearInputArea()
    Dim ws As Worksheet

    ' Save a separate copy of the workbook before running this macro.
    Set ws = ThisWorkbook.Worksheets("Input")
    ws.Range("B2:F50").ClearContents
End Sub

Change the sheet name and range to match the workbook. Avoid using ActiveSheet or Selection when a fixed worksheet and range are intended: the macro could otherwise act on whichever sheet or cells happen to be active when it runs. Explicit references make the target easier to review and reduce accidental changes.

If the goal is to clear both entries and appearance, replace ClearContents with Clear, but first check whether the range contains formatting or settings that should remain. You can also clear a single cell, such as ws.Range("B2").ClearContents, or use a larger address for a defined block.

Clipboard behavior and CutCopyMode

When a macro uses Range.Copy without a destination, Excel places the copied range into copy mode. The moving border around the copied cells is a visible sign of that state. After a paste, setting Application.CutCopyMode = False ends Excel’s copy or cut mode and removes the moving border.

That line is not a general command to erase everything a person has copied on the Windows clipboard. It resets Excel’s cut/copy state. It is also not needed after every copy: a direct copy with Destination:= avoids the separate paste step. If a macro needs to paste the range more than once, do not cancel copy mode until those pastes are finished.

For a simple values transfer with identically sized ranges, assigning values directly can avoid clipboard state altogether. However, that approach does not copy formatting, and different source and destination shapes can cause problems or produce results that are not what you intended. Confirm the range dimensions before choosing it.

Common errors and how to avoid them

Subscript out of range: A worksheet name in the code does not match a tab in the workbook, or the code is looking in a different workbook. Check the spelling and use ThisWorkbook when the macro should act on the workbook containing the code.
PasteSpecial fails: A copy operation may not have occurred, or the target and clipboard state may have changed. Make sure the source is copied immediately before the paste and that the target is a valid range.
Unexpected formatting disappears: The macro used Clear or pasted a broader set of properties than intended. Use ClearContents for a content-only reset or choose a specific PasteSpecial type.
Formulas become fixed results: The macro used xlPasteValues. That is intentional for a snapshot, but use a normal copy or xlPasteFormulas if formulas must remain.
The wrong cells change: The code relies on the active sheet, selection, or an unqualified range. Refer to the worksheet and range explicitly, and inspect those lines before running.

Also consider whether the destination contains existing data. A copy can overwrite cells in the pasted area without asking for confirmation. For a macro that runs repeatedly, you may want to check the target first, use a separate output sheet, or add a confirmation prompt appropriate to the workflow.

Run VBA safely in Excel for Windows and Mac

VBA macros run in the desktop versions of Excel, not in Excel for the web. In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11 or use Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. To run a macro, use Developer > Macros, select its name, and choose Run.

Excel for Mac also supports VBA in the desktop app. Use Developer > Visual Basic to open the editor; the keyboard shortcut may depend on the Mac keyboard and function-key settings. Standard clipboard shortcuts differ: Windows uses Ctrl+C and Ctrl+V, while Mac uses Command+C and Command+V. A macro that refers to ordinary worksheet ranges will usually work on either platform, but operating-system-specific file paths, controls, and automation may require changes.

Before running unfamiliar code, save a separate copy of the workbook. Excel’s normal undo history does not reverse changes made by a VBA macro, so Ctrl+Z is not a reliable recovery method. Only enable macros in workbooks from sources you trust, and review the ranges named in the code. If the workbook contains important records, test on a copy first.

Frequently Asked Questions

What is the VBA code to clear contents but keep formatting?
Use Range("A1:C10").ClearContents for the target range. It removes values and formulas while retaining the cells’ formatting.

How do I clear everything from a range in VBA?
Use Range("A1:C10").Clear when you intend to clear the range broadly, including its contents and formatting. Check the target carefully because other cell-level settings may be affected too.

How do I copy and paste values only in VBA?
Copy the source range, then use PasteSpecial Paste:=xlPasteValues on the destination range. This pastes calculated values rather than the source formulas.

How do I copy a range without using the clipboard?
For a standard copy, use source.Copy Destination:=target. If you need only values and both ranges have matching dimensions, assigning target.Value = source.Value is another option, but it does not copy formatting or formulas.

Can Ctrl+Z undo a VBA clear or paste?
Usually not. Running a macro clears Excel’s regular undo history, so save a separate workbook copy before running code that changes 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.