Excel VBA Copy and Paste Formatting: Code and Tips

To copy only formatting with Excel VBA, copy the source range and use PasteSpecial with xlPasteFormats: sourceRange.Copy, then destinationRange.PasteSpecial Paste:=xlPasteFormats. This transfers formatting without replacing the destination’s values or formulas.

The basic VBA pattern

Excel’s Range.PasteSpecial method accepts a paste type. The xlPasteFormats constant tells Excel to paste cell formatting rather than everything on the clipboard. The source range must first be copied, because PasteSpecial uses Excel’s copy buffer.

The shortest useful pattern is:

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

Here, sourceRange and destinationRange are Range variables already set to valid cells or ranges. Turning off CutCopyMode removes Excel’s moving copy border after the operation. It is optional, but it leaves the worksheet in a tidier state.

Use xlPasteFormats when you want the destination’s existing content to remain in place. By contrast, a normal paste can transfer values, formulas, formatting, and other copied cell attributes together.

A complete macro with explicit ranges

This example copies the formatting of A1:C10 on Sheet1 to the same-sized range on Sheet2 in the workbook containing the macro. Change the worksheet names and addresses to suit your file.

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

    ' Set the source and destination ranges explicitly.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
    Set destinationRange = ThisWorkbook.Worksheets("Sheet2").Range("A1:C10")

    ' Copy formatting without replacing destination contents.
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteFormats

    ' Clear Excel's copy mode after the paste.
    Application.CutCopyMode = False
End Sub

ThisWorkbook means the workbook where the macro code is stored. It is usually safer than relying on whichever workbook happens to be active. If the source and destination are in different workbooks, qualify each range with the appropriate workbook object instead. Avoid depending on ActiveSheet or Selection unless the macro is intentionally designed to act on the user’s current selection.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a mistaken range reference can require closing without saving or restoring a backup.

What formatting-only paste changes

A formats-only paste is intended to transfer presentation and cell-format settings, not the underlying cell contents. Typical formatting includes font, fill, borders, alignment, and number formats. For example, a source cell displayed as a date can give a destination number a date-style display while leaving the destination’s numeric value intact.

It does not mean that every visual effect in a worksheet is a simple, permanent cell format. Conditional formatting is rule-based: a cell’s appearance can change according to its value or a formula. If conditional formatting matters, inspect the destination rules and resulting appearance after the macro runs rather than assuming that a static format paste reproduces the intended behavior.

Paste formats also does not automatically make source and destination geometry identical. Row heights and column widths are separate worksheet dimensions. Merged cells, existing protection settings, and overlapping conditional-formatting rules can also make a result look different from what a reader expects. Check the specific features used in the source range.

Match the ranges and choose the right target

For predictable results, make the source and destination ranges the same shape. The example maps a 10-row-by-3-column block to another 10-by-3 block, so each source cell has an obvious corresponding destination cell. If the ranges differ in size, Excel’s paste behavior may not match the intended layout; test the exact case in a disposable copy rather than relying on an assumed one-to-one mapping.

Same-size block: Use matching row and column counts when transferring a table’s formatting.
One cell to another: Set both variables to individual cells when copying one cell’s style.
Entire column or row: Use a full-column or full-row reference only when you really want to affect that entire worksheet dimension.
Nonadjacent cells: Handle separate areas deliberately; a multi-area selection can be less predictable than a loop over individual ranges.

For example, formatting a heading in A1 and applying it to a heading in D1 is a straightforward one-cell-to-one-cell transfer. Applying a multi-column table style from A1:C10 to a differently shaped block such as E1:F15 is not an equivalent mapping. Decide which source format should correspond to each target cell before broadening the macro.

Copy formats between workbooks

The same method works across open workbooks, but explicitly identify both workbooks and worksheets. This avoids pasting into the wrong active file. The following example assumes both workbooks are open and their names match the names shown in Excel’s title bar.

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

    ' Identify both open workbooks and the matching ranges.
    Set sourceRange = Workbooks("Source.xlsx").Worksheets("Summary").Range("A1:C10")
    Set destinationRange = Workbooks("Report.xlsx").Worksheets("Summary").Range("A1:C10")

    ' Transfer formats only, then leave copy mode.
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

The workbook names must match the open workbooks, including their file extensions. If a workbook is not open, this reference will not find it. Open the required files first, or write additional VBA to open and close them safely. If the macro is stored in the source workbook, use ThisWorkbook for that workbook and a specifically named Workbook reference for the other one.

Column widths, row heights, and other paste choices

Cell formatting and worksheet dimensions are distinct. To copy column widths, Excel provides the separate paste type xlPasteColumnWidths. For example, copy the source columns and paste their widths into the destination columns. This is a separate operation from pasting cell formats, and it can change the layout for every cell in those columns.

Row heights likewise need separate handling; formats-only paste should not be treated as a general row-height transfer. VBA can set row heights directly when that is required, but first decide whether the source and target rows should have identical heights. A height change affects the whole row, not just the cells in the copied range.

Other PasteSpecial constants support other goals. For instance, values-only pasting and formulas-only pasting are different from formats-only pasting. Choose the paste type that matches the requested result; do not substitute a normal paste if preserving destination content is essential.

Common problems and how to fix them

“PasteSpecial method of Range class failed”: Confirm the source range was copied immediately before the paste, that both ranges are valid, and that Excel is not in a state that prevents the paste. Try the macro on a small range in a saved test copy.
The wrong sheet or workbook changes: Replace unqualified references such as Range("A1") with a fully qualified worksheet and workbook reference.
Values or formulas were overwritten: Check that the code uses Paste:=xlPasteFormats, not a regular paste or another paste type.
Only part of the expected area changes: Compare source and destination dimensions, and check for merged cells or multi-area ranges. Use matching rectangular ranges where possible.
The destination looks different afterward: Review conditional-formatting rules, number formats, row heights, column widths, and merged cells. A visual result can depend on more than the static cell format.
The macro does not run: Save the workbook in a macro-enabled format such as .xlsm, and check that macros are allowed by your organization’s security settings.

When a macro fails, do not immediately add broad error suppression such as On Error Resume Next. That can hide the real cause and leave a partially changed workbook. Check the highlighted VBA line, worksheet names, range addresses, and whether the expected workbooks are open.

Running the macro safely on Windows and Mac

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from the Developer tab by choosing Visual Basic. If the Developer tab is not visible, it can be enabled through File > Options > Customize Ribbon. Add the procedure to a standard module, then run it from the editor or the Macro dialog.

Excel for Mac also includes VBA and the Visual Basic Editor, though keyboard shortcuts and settings can vary with the keyboard and Excel release. The Windows Alt+F11 shortcut may require the Fn key or may not be available as-is on a Mac; use the Developer tab’s Visual Basic command when possible. Excel for the web can open a workbook containing VBA, but it cannot run VBA macros. Use desktop Excel to execute this code.

Only enable macros in workbooks from sources you trust. A macro that copies formats can still alter a large range if its references are wrong. Keep a backup, review the code and its target ranges, and test changes on a small sample before using it on important data.

Frequently Asked Questions

How do I copy formatting to another cell in Excel VBA?
Set a source Range and a destination Range, call sourceRange.Copy, then call destinationRange.PasteSpecial Paste:=xlPasteFormats. Clear copy mode afterward if desired.

Will PasteSpecial xlPasteFormats copy values or formulas?
No. That paste type is for formatting, so the destination’s existing values and formulas are not replaced by the source contents. Use a different paste type only if you also intend to transfer content.

Can VBA copy formatting to an entire column?
Yes. Assign the relevant whole-column ranges to the source and destination variables and use the same formats-only paste method. Be cautious: a whole-column operation can affect far more cells than a table-sized range.

How can I copy column widths as well as formats?
Copy and paste the cell formats, then perform a separate paste using xlPasteColumnWidths on the corresponding columns. Row heights are also separate and need their own handling.

Can I undo a formatting macro with Ctrl+Z?
No. Running VBA generally clears Excel’s undo history, and macro changes cannot be reversed with Ctrl+Z. Save a backup copy before running the procedure.

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.