VBA & Macro Problems

How to Fill Excel Formulas Down Without Copying Formatting in VBA

WPS Editor Sep 30, 2026 868 views

Question details

The user needs to copy or fill formulas down across multiple rows in an Excel VBA macro without overwriting the existing cell formatting or splitting conditional formatting rules.

How to Fill Excel Formulas Down Without Copying Formatting in VBA

Product: Excel
Device & OS: not provided
Scenario: Writing and executing a VBA macro to distribute formulas across a specific range while keeping the destination's visual structure intact.
Observed behavior: Using the standard VBA FillDown method duplicates both the formula and the cell formatting, which overrides existing styles and fragments conditional formatting rules into multiple separate ranges.

Before you start

Before modifying and running VBA code, ensure your workbook is saved as a macro-enabled file (.xlsm) and that you have a backup of your data to prevent accidental loss of complex cell formatting.

Solution 1Recommended

Use the PasteSpecial Method (Recommended)

Utilize the PasteSpecial VBA method with the xlPasteFormulas argument to paste only the formula, completely bypassing formatting duplication.

This approach copies the source range into the clipboard and explicitly pastes only the formulas into the destination. It is highly reliable for preserving existing cell colors, borders, and conditional formatting rules.

Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) editor.

Insert or Select a Module

Click 'Insert' from the top menu and select 'Module' to create a blank script window, or open an existing macro module.

Enter the PasteSpecial Code

Type the macro code using Copy and PasteSpecial. For example: Range("H13:L13").Copy Range("H14:L22").PasteSpecial xlPasteFormulas

Run the Macro

Press F5 or click the 'Run' icon on the toolbar. The formulas will be copied down without altering the destination's conditional formatting.

Use the PasteSpecial Method (Recommended)

Clear Clipboard Memory: It is good practice to add 'Application.CutCopyMode = False' at the end of your macro to clear the clipboard and remove the 'marching ants' highlight from the copied cells.

Solution 2

Use the AutoFill Method with xlFillValues

Apply the AutoFill method paired with the xlFillValues type to mimic the drag-to-fill handle behavior without copying the format.

WPS VBA Support

Execute Macros to Fill Formulas in WPS Spreadsheet

WPS Office provides outstanding support for VBA macros. You can effortlessly run the same Excel macros to fill formulas down while keeping your conditional formatting intact, all within a more lightweight and streamlined environment.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled spreadsheet document.
2. Enable Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If prompted by a security warning, choose to enable macros for the current document.
3. Access the Visual Basic Editor: Click the 'Visual Basic' icon in the Developer ribbon or press Alt + F11 to launch the integrated code editor.
4. Run the PasteSpecial Macro: Input your macro code utilizing PasteSpecial xlPasteFormulas into a module and click 'Run' to update your formulas flawlessly.

Highly compatible with Microsoft Excel VBA macro code and syntaxPreserves complex conditional formatting during formula copiesLightweight installation with an intuitive, tabbed user interfaceFree alternative for managing large datasets and automation

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard FillDown method ruin my conditional formatting?

The standard VBA FillDown method copies the entire cell object. This includes formulas, borders, background colors, and conditional formatting rules. When executed over existing rules, it overrides them or duplicates the logic, resulting in fragmented rule ranges in the Conditional Formatting Manager.

Can I copy formulas without formatting using keyboard shortcuts instead of VBA?

Yes. Select and copy the cell containing your formula (Ctrl + C). Then, highlight your destination range and press Alt, E, S, F (which opens Paste Special and selects Formulas). Press Enter to apply.

What is the difference between xlFillValues and xlPasteFormulas?

While both yield a similar visual result, PasteSpecial (xlPasteFormulas) is a clipboard-based operation that copies and pastes formula text. AutoFill (xlFillValues) mimics a user manually dragging the cell's corner fill handle downward, processing it without engaging the system clipboard.

Excel & Data Problems