VBA & Macro Problems

How to Use VBA to Copy a Formula into an Inserted Row in Excel

Rana Garcia Oct 13, 2026 870 views

Question details

The user needs a VBA macro code snippet to copy a formula from a specific column in the preceding row into a newly inserted active row.

How to Use VBA to Copy a Formula into an Inserted Row in Excel

Product: Excel
Device & OS: not provided
Scenario: Writing or modifying a VBA macro that inserts a new row and selectively copies values and formulas from the row above.
Observed behavior: The current macro successfully copies data in some columns, but lacks the specific code required to copy the formula in column G without overwriting other data.

Before you start

Before modifying your macro, verify which row is currently defined as the ActiveCell in your script, as the relative references depend on the active row's position.

Solution 1Recommended

Use Range.Copy and PasteSpecial in Your Macro

You can append a specific copy-and-paste command to your existing macro to target column G of the preceding row and paste only the formula into the new row.

To achieve this, you need to reference the row immediately above the newly inserted row, copy the specific cell, and use the xlPasteFormulas property so you don't overwrite cell formatting.

Open the VBA Editor

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

Locate Your Existing Macro

Find the module containing the code that currently inserts the new row and copies columns B, C, and N through P.

Add the Copy Command

Directly after your row insertion code, add the line: Range("G" & ActiveCell.Row - 1).Copy

Add the PasteSpecial Command

On the next line, add the code to paste the formula: Range("G" & ActiveCell.Row).PasteSpecial Paste:=xlPasteFormulas

Test the Macro

Save your code and run the macro to ensure the row is inserted and the formula in column G is correctly copied down.

Use Range.Copy and PasteSpecial in Your Macro

Relative Cell Referencing: This code assumes your active cell is located in the newly inserted row. If your macro selects a different row after insertion, adjust 'ActiveCell.Row' accordingly.

Advanced Macro Support

Write and Run VBA Macros Easily in WPS Spreadsheet

WPS Office offers robust VBA support, allowing you to seamlessly write, edit, and execute your Excel macros to automate tasks like inserting rows and copying formulas.

1. Download and Install: Download WPS Office for free from the official website and complete the installation.
2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing macro-enabled workbook (.xlsm).
3. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click 'Visual Basic' to view and run your VBA code.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formats.Built-in VBA editor makes it simple to write and debug scripts.Lightweight architecture ensures your automated tasks run quickly and efficiently.Free to use with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA code copy the value instead of the formula?

If you assign values directly using an equals sign (e.g., Range1.Value = Range2.Value) or use standard paste commands, the underlying formula is often lost. You must use the PasteSpecial method with the Paste:=xlPasteFormulas parameter to ensure the formula itself is duplicated.

Can I copy formulas for multiple specific columns at once in VBA?

Yes. While you can handle them individually, you can also use a union or specify a larger range, such as Range("N" & ActiveCell.Row - 1 & ":P" & ActiveCell.Row - 1).Copy, and then apply PasteSpecial to the target range.

How do I avoid syntax errors when combining text and variables in VBA?

Ensure you correctly use the ampersand (&) to concatenate strings and variables. For example, Range("G" & ActiveCell.Row) correctly combines the letter G with the numeric row value to form a valid cell reference like G5.

Excel & Data Problems