Excel VBA Copy and Paste Formatting Without Changing Values

To copy formatting with Excel VBA without copying cell values or formulas, copy the source range and paste with xlPasteFormats. For example, use sourceRange.Copy followed by targetRange.PasteSpecial Paste:=xlPasteFormats; the destination keeps its existing contents while receiving the source formatting.

What “paste formatting” copies

Excel cell formatting is a collection of visual and display settings, not just a font color. Pasting formats can apply the source cells’ number formats, font settings, fill, borders, alignment, and related cell-format properties. For instance, a source cell displaying a date in a long format can give a destination cell the same date display, while leaving the destination’s underlying date value intact.

That distinction is useful when a worksheet already contains data and you want a consistent appearance. A formats-only paste does not replace the destination’s value or formula with the source’s. It also does not mean “copy only the font”: if you need a narrower change, use VBA properties such as .Font, .Interior, or .NumberFormat deliberately rather than pasting the entire set of formats.

Some related features need separate attention. Column widths and row heights are not ordinary cell formatting pasted by xlPasteFormats. Conditional formatting rules can also affect what you see after a paste, so inspect the destination’s rules if its appearance changes unexpectedly. Merged cells and cells with different layouts can make a block paste harder to predict.

A basic VBA macro for a matching range

This example copies the formatting from B2:D6 to F2:H6 on a worksheet named Sheet1. Both ranges have the same size, which makes the source-to-destination correspondence clear: B2’s format goes to F2, C2’s to G2, and so on.

Option Explicit

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

    ' Change the sheet name and ranges to fit the workbook.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceRange = ws.Range("B2:D6")
    Set targetRange = ws.Range("F2:H6")

    ' Paste formatting only; destination contents remain in place.
    sourceRange.Copy
    targetRange.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

Before running a macro, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the safest way to recover from an unintended paste. The example uses ThisWorkbook, meaning the workbook containing the macro. If the macro is stored in a different workbook from the data, change the workbook reference rather than assuming the active workbook is the right one.

How to put the macro in Excel and run it

Save a backup, then save the working file in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code.
Open the Visual Basic Editor. On Windows, press Alt+F11. On Mac, Option+F11 may work; some keyboards require Fn+Option+F11.
In the editor, choose Insert > Module, then paste the procedure into the module. Update the worksheet name and range references.
Return to Excel and run the macro from Developer > Macros: select CopyFormattingOnly and choose Run. If the Developer tab is not visible, enable it in Excel’s ribbon settings.

Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Open the workbook in the desktop Excel application to use this code. The VBA operation is essentially the same in desktop Excel for Windows and Mac, although keyboard shortcuts and editor access can depend on the Mac’s function-key settings.

Copying one cell’s format across many cells

A common variation is to use one example cell as a template for a larger destination range. Copying a single cell and pasting formats across a range applies that cell’s formatting to the destination cells; it does not fill the range with the source value. This is useful for applying a standard input style to newly added rows.

Option Explicit

Sub ApplyOneCellFormat()
    Dim ws As Worksheet
    Dim sourceCell As Range
    Dim targetRange As Range

    ' Use a template cell and a destination on the same worksheet.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceCell = ws.Range("B2")
    Set targetRange = ws.Range("D2:D20")

    ' Apply B2's formats to D2:D20 without copying B2's contents.
    sourceCell.Copy
    targetRange.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

Choose the template cell carefully: the macro applies all of its cell formatting, including its number format. If B2 is formatted as a percentage, for example, the destination cells receive a percentage display even if their existing values are ordinary numbers. The values remain, but their displayed appearance can change because number formatting changes how Excel presents them.

Matching ranges, different sizes, and worksheet references

For a block-to-block paste, equal-sized source and destination ranges are the clearest and least surprising choice. If the dimensions differ, Excel may repeat or distribute copied cells according to its paste behavior, and the resulting layout may not correspond to what the macro’s author intended. When a source contains several different formats, use destination ranges with a deliberate matching shape and verify the result.

The examples use ranges on one worksheet to make the mapping easy to follow. To copy between sheets, define each range from its own worksheet, for example by setting a source worksheet and a target worksheet separately. This avoids relying on whichever sheet happens to be active when the macro runs. Similarly, qualify ranges with a worksheet object instead of writing an unqualified Range("B2"); unqualified references can point to the active sheet and cause errors or modify the wrong place.

For a repeatable workbook tool, consider validating assumptions before copying: confirm that the named sheets exist, that the target range is the intended area, and that it is not protected against formatting changes. A simple macro that targets a fixed range is convenient for a stable template, but it should be adjusted if users insert rows or move the relevant cells.

Common problems and how to fix them

“PasteSpecial method of Range class failed.” Check that the copy step ran immediately before the paste, that the ranges are valid, and that the destination sheet or cells are not protected in a way that blocks formatting changes. Avoid switching workbooks or changing selection between the two operations.
The destination looks different from the source afterward. Check number formats and conditional formatting rules. A cell can have the same base fill and font yet display differently because a conditional formatting rule applies to it. Use Home > Conditional Formatting > Manage Rules to review rules for the relevant selection or worksheet.
Column widths did not change. That is expected: xlPasteFormats does not copy column widths as part of the cell-format paste. Excel VBA has a separate paste type, xlPasteColumnWidths, for column widths. Row heights generally need to be set separately through row properties.
A merged range or uneven layout causes an error. Make sure the source and destination have compatible merged-cell layouts, or unmerge and redesign the ranges before running the macro. Pasting a rectangular block into a differently merged area is a frequent source of failures.
The macro is blocked or disappears after saving. Use a macro-enabled workbook format to retain VBA, and follow the organization’s macro security policy. Do not enable macros in files from untrusted sources.

After testing on a copy, inspect representative destination cells rather than assuming the paste affected only the visible properties you had in mind. If a workbook uses table styles, conditional formatting, or merged headings, check those areas specifically.

Formats-only paste versus other approaches

xlPasteFormats is a good choice when the goal is to reproduce a cell or range’s complete formatting without its contents. It closely matches the intent of Excel’s Paste Special > Formats command and is easy to adapt to a fixed source and destination.

For a one-off task, the ribbon may be simpler: copy the source, select the destination, then use Home > Paste > Paste Special > Formats. The Format Painter is another visual option for copying appearance without writing VBA. On Windows, Ctrl+C and Ctrl+V are the standard copy and paste shortcuts; on Mac, use Command+C and Command+V. In the Paste Special dialog, choose the Formats option rather than a regular paste.

Use direct property assignment when you want only one aspect to change. For example, setting a destination range’s .NumberFormat can standardize date or currency display without replacing its font, fill, and borders. Assigning the source’s entire .Font object can address font settings, but it does not copy all other cell-format categories. The trade-off is control versus completeness: property assignments are more targeted but require you to specify each property you intend to transfer.

Choosing a reliable method for recurring work

For a fixed template with known ranges, a short VBA procedure using xlPasteFormats is usually straightforward. For occasional manual edits, the ribbon or Format Painter avoids maintaining code. For a dynamic report, build in checks for the destination range and consider whether the source should be a single template cell or a full sample row.

Choose xlPasteFormats when you want the normal Excel formats-only paste behavior for an entire cell range.
Choose specific VBA properties when only a defined characteristic, such as number format or font color, should change.
Handle widths, heights, conditional formatting, and merged-cell structure as separate concerns when those matter to the finished sheet.
Test the macro with representative data and save a backup before running it on an important workbook.

A common misconception is that formatting-only means “copy the appearance but preserve how every value is displayed.” It preserves the destination contents, but the source number format can change how those contents appear. A value of 0.25, for example, may display as 25% after receiving a percentage format; the stored value remains 0.25. Keep that difference between stored data and displayed format in mind when applying a template.

Frequently Asked Questions

What is the VBA code to paste formats only?
Copy the source range, then use targetRange.PasteSpecial Paste:=xlPasteFormats. This applies formats without pasting the source values or formulas.

Does xlPasteFormats copy values or formulas?
No. It applies formatting to the destination and leaves its existing values and formulas in place. However, number formats can change how those contents appear on screen.

Can I copy formatting to a different worksheet?
Yes. Set the source range and destination range using their respective worksheet objects, then copy and paste formats. Qualifying both ranges is safer than relying on the active sheet.

Does xlPasteFormats copy column widths?
No. Column widths are separate from the cell formats copied by xlPasteFormats. Use the dedicated xlPasteColumnWidths paste type or set the destination column width separately.

Can I undo a VBA formatting paste with Ctrl+Z?
Usually not. Running a VBA macro clears Excel’s normal undo history, so save a backup before running a macro that changes a workbook.

Can I run this 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 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.