VBA & Macro Problems

How to Use Excel VBA Macro to Insert a Row and Copy Formulas

Huda Qurayshi Oct 8, 2026 868 views

Question details

The user needs a VBA macro that can prompt for a row number, insert a new worksheet row, copy formulas from columns G through M of the preceding row, and apply data validation to the new cell.

How to Use an Excel VBA Macro to Insert a Row and Copy Formulas

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating repetitive data entry tasks by inserting a new worksheet row and seamlessly carrying over complex formulas and validation rules from the row directly above it.
Observed behavior: Upon running the macro, a new row is inserted at the specified location, populated with the correct formulas in columns G through M, and configured with the required data validation dropdown.

Before you start

Always test new VBA macros on a backup copy of your workbook to prevent accidental data loss, and ensure your file is saved as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Create and Run a Custom VBA Macro

Use a specialized VBA script that prompts the user for a row number, inserts the row, and automatically copies the preceding row's formulas and validation rules.

This solution utilizes an InputBox to ask for the exact row number. It then shifts the existing data down, duplicates the formulas from columns G through M of the row above, and recreates the specific data validation dropdown required for the new entry.

Open the VBA Editor

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

Insert a New Module

In the VBA Editor, click on 'Insert' in the top menu and select 'Module' to create a blank workspace for your code.

Paste the Macro Code

Copy and paste the following script into the module window: Sub InsertRowAndFillDown() Dim RowNum As Long RowNum = InputBox("Please enter the row number to create a new line entry", "New Line Entry") If RowNum = 0 Then Exit Sub Rows(RowNum & ":" & RowNum).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Cells(RowNum, 7).Formula = Cells(RowNum - 1, 7).Formula Range(Cells(RowNum, 8), Cells(RowNum, 13)).Formula = Range(Cells(RowNum - 1, 8), Cells(RowNum - 1, 13)).Formula With Range("G" & RowNum).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='Uni Codes'!$A$2:$A$4000" .IgnoreBlank = True .InCellDropdown = True End With End Sub

Run the Macro

Close the VBA Editor. Press Alt + F8, select 'InsertRowAndFillDown' from the Macro dialog box, and click 'Run'. Enter your target row number when prompted.

Worksheet References: When working with multiple sheets, ensure you qualify 'Rows', 'Cells', and 'Range' with specific worksheet references (e.g., Worksheets("Sheet1").Rows) to avoid runtime errors.

Automate Spreadsheet Tasks with WPS Office

WPS Spreadsheet provides a robust environment for managing complex datasets, formulas, and data validation. Supported versions also allow you to utilize macros and VBA, making it incredibly easy to automate tasks such as inserting rows and copying formulas.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon to access the Macro and Visual Basic features.
3. Insert and Run the Macro: Click on 'Visual Basic', insert a new module, paste your row-insertion VBA code, and run it to automate your task.

High compatibility with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Advanced formula and data validation support to automate your workflow.Lightweight, fast, and features a familiar tabbed interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro copying the formulas correctly?

This usually happens if the macro is executed while the wrong sheet is active. Ensure your macro code explicitly references the correct worksheet, such as adding Worksheets("YourSheetName") before the Rows and Cells commands.

Can I modify this macro to apply to multiple rows at once?

Yes. The provided script is designed for a single row based on user input. To insert multiple rows, you would need to modify the script to loop through a specified range or prompt the user for both a start and an end row number.

How do I save a workbook that contains VBA macros?

Workbooks containing macros cannot be saved as standard .xlsx files. When saving, you must choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the 'Save as type' dropdown menu to preserve the VBA code.

Excel & Data Problems