VBA & Macro Problems

How to Copy Excel Formulas into Every Third Row with VBA

Guest Writer Oct 9, 2026 872 views

Question details

The user needs to automatically copy a specific block of formulas and paste them into target rows where a specific condition is met, such as copying every third row.

Product: Excel
Device & OS: not provided
Scenario: Automating the process of copying a block of formulas and pasting them into corresponding target rows based on a specific cell value in column A.
Observed behavior: The user wants to paste formulas in offset rows while maintaining an intentionally blank middle row, ensuring the formulas match up beside the trigger values.

Before you start

Before running any VBA scripts, ensure you have saved a backup copy of your workbook, as macro actions cannot typically be undone.

Solution 1Recommended

Use a VBA Macro with PasteSpecial for Formulas

Create a custom VBA script to loop through the target column, find matching conditions, and securely paste only the formulas.

This VBA approach optimizes the process by utilizing the PasteSpecial method specifically for formulas. To ensure fast execution on large datasets, it is highly recommended to temporarily disable screen updating and automatic calculations while the script runs.

Open the VBA Editor

Press the ALT + F11 keys on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

In the top menu, click 'Insert' and then select 'Module' to create a blank script window.

Write the Loop and Copy Logic

Write a VBA For Each loop that checks column A for your trigger value (e.g., '4'). Inside the loop, instruct VBA to copy your source range (e.g., Range("B3:AF5").Copy).

Apply PasteSpecial

Use the PasteSpecial method on the destination cell (offset by one column to the right) with the parameter xlPasteFormulas to insert the copied formulas without overwriting cell formatting.

Run the Macro

Press F5 or click the green 'Run' button in the toolbar to execute the macro and populate your spreadsheet.

Use a VBA Macro with PasteSpecial for Formulas

Performance Optimization: Add 'Application.ScreenUpdating = False' at the start of your macro and 'Application.ScreenUpdating = True' at the end to prevent screen flickering and drastically speed up the macro.

Efficient Spreadsheet Automation

Automate Workflows seamlessly with WPS Spreadsheets

WPS Office provides robust, built-in support for VBA and macros, allowing you to easily run custom scripts to copy and paste formulas just as you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheets and open your .xlsm or .xlsx file.
2. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click the 'VBA Editor' button.
3. Insert and Paste Code: Insert a new module and paste your formula-copying VBA script into the code window.
4. Run the Automation: Run the macro to instantly apply the targeted formulas across your entire worksheet.

Full support for advanced VBA macros and scriptingHighly compatible with Microsoft Excel files (.xlsx, .xlsm)Free, lightweight, and fast performance for large datasets

Frequently Asked Questions

Why should I use xlPasteFormulas instead of a regular paste?

Using xlPasteFormulas ensures that only the calculation logic is copied into the destination cells. A regular paste would also overwrite the destination cells with the background colors, borders, and fonts of the source cells.

How do I enable the Developer tab to access the VBA Editor?

Go to File > Options > Customize Ribbon, and check the box next to 'Developer' in the right pane. This will add the Developer tab to your main ribbon, granting easy access to macros and the VBA Editor.

Will this VBA macro process rows that are hidden or filtered out?

By default, a standard VBA loop will process all cells in the specified range, including hidden rows. If you only want the macro to evaluate visible cells, you must append '.SpecialCells(xlCellTypeVisible)' to your range definition.

Excel & Data Problems