VBA & Macro Problems

How to Use VBA to Insert Matching Excel Rows at Row 5

Maira Mehtab Oct 21, 2026 868 views

Question details

The user needs an Excel VBA macro to find specific matching data in one sheet, insert that data starting exactly at row 5 on a second sheet, and ensure no existing data is overwritten while preserving the original formatting.

How to Use VBA Macro to Insert Matching Excel Rows at Row 5

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction of specific rows based on criteria and inserting them into a designated location (Row 5) of another worksheet, shifting existing data down.
Observed behavior: The correct operation must count the total matches, insert the exact number of blank rows at Row 5, and perform a PasteSpecial with source theme retention.

Before you start

Ensure you have saved a backup copy of your workbook before running any VBA macros, and verify that the Developer tab is enabled in your spreadsheet ribbon.

Solution 1Recommended

Use a Two-Pass VBA Macro to Count Matches and Insert Space

This method counts the number of qualifying rows first, inserts the exact number of empty rows at row 5, and then copies the data over. This prevents existing data from being overwritten.

To avoid overwriting existing data, the macro must calculate how much space is needed before performing any copy actions. Once the space is dynamically inserted using the Resize and Insert commands, a second loop copies the data into the new blank rows.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. Click 'Insert' from the top menu and select 'Module' to create a new blank script window.

Define Worksheets and Calculate Matches

Write a script that loops through your source sheet (e.g., Sheet A) to count how many rows meet your criteria. Use a variable like matchCount to track this: For i = 1 To lastRowA ... If condition met Then matchCount = matchCount + 1.

Insert Blank Rows at Row 5

Add the command to shift the existing rows down on your destination sheet (Sheet B) based on the match count: wsB.Rows(5).Resize(matchCount).Insert Shift:=xlDown

Copy and Paste Data Using Source Theme

Loop through Sheet A one more time. When a row meets the criteria, copy the range and paste it into the newly created blank rows on Sheet B using PasteSpecial Paste:=xlPasteAllUsingSourceTheme. This guarantees the original cell colors, borders, and fonts are preserved.

Run the Macro

Close the VBA editor or press F5 while inside the script to run it. Verify that your destination sheet now has the new data starting at row 5, with all previous data shifted safely below.

Use a Two-Pass VBA Macro to Count Matches and Insert Space

Clear Clipboard Memory: Always include 'Application.CutCopyMode = False' at the end of your VBA script to clear the system clipboard and prevent Excel from displaying marching ants around your copied cells.

WPS Spreadsheet Macro Support

Automate Data Processing with WPS Spreadsheet

WPS Office provides robust VBA support, allowing you to run the exact same macros to automate data extraction, insertion, and manipulation effortlessly.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing the data you want to manipulate.
2. Access the VBA Editor: Navigate to the 'Tools' tab on the top ribbon and click on 'Macros' followed by 'Visual Basic Editor'.
3. Insert the Code: Click 'Insert' > 'Module' and paste your row-inserting VBA script into the code window.
4. Execute the Macro: Press F5 or click the green 'Run' button. Return to your worksheet to see the matching rows automatically inserted at Row 5.

Fully compatible with Microsoft Excel VBA macro scriptsLightweight, fast execution for large datasetsFree built-in macro editor (Visual Basic Editor)Intuitive tabbed interface for seamless document management

microsoft office alternative - wps office

Frequently Asked Questions

Why do I need to count the matches before copying the rows?

Counting the matches first tells the macro exactly how many blank rows it needs to insert at Row 5. Without this step, pasting the data would immediately overwrite the existing data on the destination sheet.

How do I retain the original cell formatting when pasting with VBA?

Instead of using the standard Paste command, utilize 'PasteSpecial Paste:=xlPasteAllUsingSourceTheme' in your VBA script. This preserves the exact fonts, borders, cell colors, and number formats from the source sheet.

Can I change the insertion row from Row 5 to another row?

Yes. You can easily modify the target row by changing the row index parameter in your code. For instance, replacing 'wsB.Rows(5)' with 'wsB.Rows(10)' will insert the new matching data starting at row 10.

Excel & Data Problems