VBA & Macro Problems

How to Use Excel VBA to Insert a Row and Copy Formulas from Below

Natalie Taylor Oct 10, 2026 869 views

Question details

The user needs an Excel macro assigned to an 'Insert' button that adds a new row and copies formatting, validation, dropdowns, and formulas from the row below, without keeping its static data values.

How to Use Excel VBA to Insert a Row and Copy Formulas from Below

Product: Excel
Device & OS: not provided
Scenario: Automating worksheet data entry by inserting fully formatted rows that inherit necessary structural controls and calculations from adjacent rows.
Observed behavior: A standard row insertion requires manual copying and deleting of values. The goal is to automate the insertion process so the new blank row inherits structural properties and formulas automatically.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that you save your workbook as a Macro-Enabled Workbook (.xlsm) to preserve the VBA code.

Solution 1Recommended

Use VBA PasteSpecial to Copy Formatting and Formulas

Use the EntireRow.Insert method combined with PasteSpecial to insert the row and paste only formulas and formatting from the row below.

This VBA script first inserts a new row at your specified range while pulling the formatting from the right or below using the xlFormatFromRightOrBelow origin. Then, it copies the row immediately below the new row and uses PasteSpecial to bring over the formulas without copying the static text values.

Open the VBA Editor

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

Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank workspace for your macro.

Paste the Macro Code

Copy and paste the following code into the module window: Private Sub CommandButton1_Click() Sheets("Record").Activate Range("A4").Select ActiveCell.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow A = ActiveCell.Row + 1 Rows(A).Copy Rows(A - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub

Assign Macro to a Button

Close the VBA Editor. Go to the Developer tab, insert a Command Button or a Shape, right-click it, and select 'Assign Macro' to link it to your newly created script.

Use VBA PasteSpecial to Copy Formatting and Formulas

Customize the Range and Sheet Name: Make sure to update 'Sheets("Record").Activate' and 'Range("A4")' in the provided code to match the actual name of your worksheet and the target cell where the insertion should take place.

Advanced Spreadsheet Automation

Automate Workflows Using WPS Spreadsheet Macros

WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to automate repetitive tasks like inserting rows and copying formulas with ease. It offers a familiar developer interface that handles complex automation seamlessly.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the options menu, and ensure the Developer tools are enabled to access macro features.
2. Open the VBA Editor: Navigate to the Developer tab and click on 'Macros' or 'Visual Basic' to launch the integrated code editor.
3. Run Your Custom Scripts: Insert a module, paste your Excel VBA code, and assign it to form controls just as you would in standard Excel environments.

Seamless compatibility with Microsoft Excel VBA scripts and macro-enabled workbooksFamiliar developer interface and macro editorFast processing for large datasets and complex formulasHighly lightweight yet powerful alternative for spreadsheet automation

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro copy text values instead of just formulas?

This happens if you use a standard Paste operation instead of PasteSpecial. Using PasteSpecial with the Paste:=xlPasteFormulas parameter ensures only the underlying calculations are transferred, leaving text values blank.

How do I preserve data validation and dropdown lists when inserting a row?

Setting the CopyOrigin parameter to xlFormatFromRightOrBelow during the EntireRow.Insert method automatically applies the formatting, structural properties, and data validation from the adjacent row beneath it.

Can I assign this VBA macro to a shape instead of a command button?

Yes. You can insert any shape from the Insert tab, right-click the shape, select 'Assign Macro', and choose your created VBA sub-procedure from the list. The shape will now function as an Insert button.

Excel & Data Problems