VBA & Macro Problems

How to Create an Excel VBA Macro to Copy Matching Rows to Separate Worksheets

Nimra Malik Oct 10, 2026 869 views

Question details

The user needs an Excel VBA script to read criteria from one sheet, search for matching data in other sheets, and extract the matching rows into dynamically created worksheets in a newly saved workbook.

How to Create an Excel VBA Macro to Copy Matching Rows to Separate Worksheets

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction of large datasets by pulling matching rows from source sheets (Main_Data, Mreq) based on unique values in a reference sheet (UAT) into separate worksheets.
Observed behavior: The goal is to automatically generate a new workbook containing distinct, properly named worksheets for each reference value, populated with matched rows and headers, and saved with a date-and-time filename.

Before you start

Ensure you have enabled the Developer tab in your spreadsheet application and save your original workbook as a Macro-Enabled Workbook (.xlsm) before testing new VBA scripts to prevent data loss.

Solution 1Recommended

Write and Execute the Data Extraction VBA Macro

Use this structured VBA logic to loop through your criteria, create validated worksheets, and cleanly copy matching rows from multiple source sheets without overwriting data.

This solution involves writing a custom macro that loops through the 'UAT' sheet, evaluates each value, and generates a corresponding worksheet in a new destination workbook.

To ensure the script runs smoothly, it is crucial to use dynamic row variables to find the next empty row when pasting data from 'Main_Data' and 'Mreq'. Validating worksheet names prevents errors caused by illegal characters or duplicate names.

Open the VBA Editor

Open your workbook containing the 'UAT', 'Main_Data', and 'Mreq' worksheets. Press Alt + F11 to launch the Visual Basic Editor, then click Insert > Module to create a blank script window.

Define Variables and Add a New Workbook

Write the initial code to declare your source worksheet variables (wsUAT, wsMain, wsMreq) and use Workbooks.Add to create the destination workbook where the separate sheets will reside.

Create the Primary Loop

Set up a For or Do While loop to iterate through the unique values in Column A of the 'UAT' sheet. Include a check to ensure a worksheet with that name does not already exist in the destination workbook before using Sheets.Add.

Search and Copy Matching Rows

Inside your loop, search Column A of 'Main_Data' and 'Mreq' for the current UAT value. When a match is found, copy the entire row and paste it into the new worksheet using a dynamically calculated last row (e.g., Cells(Rows.Count, 1).End(xlUp).Row + 1) to prevent overwriting.

Save with a Timestamp

Add a command at the end of the script to save the newly populated workbook automatically. Use the ActiveWorkbook.SaveAs method combined with Format(Now, "yyyymmdd_hhmm") to append a timestamp to your desired file path.

Write and Execute the Data Extraction VBA Macro

Worksheet Naming Rules: Excel limits worksheet names to 31 characters and forbids special characters like brackets, slashes, or asterisks. Ensure your VBA script includes a validation step to clean 'UAT' values before attempting to name new worksheets.

Automate Data Processing with WPS Spreadsheet Macros

WPS Office Spreadsheet provides excellent support for VBA macros, enabling you to run complex data extraction, filtering, and reporting scripts identical to Microsoft Excel. Streamline your workflow by automating repetitive row-copying tasks without needing a costly software subscription.

1. Open Your Data File in WPS: Launch WPS Spreadsheet and open your .xlsm file containing the 'UAT', 'Main_Data', and 'Mreq' sheets.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon. If it is hidden, you can enable it from the WPS settings menu.
3. Paste and Run the Script: Click 'Visual Basic' or 'Macro', insert a new module, paste your custom row-extraction VBA code, and press F5 to execute the script.
4. Review the Output: WPS will instantly generate your newly separated worksheets, copy the relevant rows, and save the timestamped workbook to your designated folder.

Full support for standard VBA scripting and Developer toolsSeamless compatibility with Microsoft Excel macro-enabled formats (.xlsm)Lightweight architecture handles large datasets and extensive loops smoothlyFree to use for daily spreadsheet processing and office management

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro crash when creating new worksheet names?

This usually occurs if the criteria values in your source sheet exceed 31 characters or contain invalid characters like / \ ? * : [ ]. You must write a helper function in your VBA script to truncate long names and replace forbidden characters before applying them to new sheets.

How can I prevent duplicate worksheets from being created?

You should include an error-handling routine or a custom function that loops through existing sheet names in the destination workbook. If a sheet with the current name already exists, the script should skip the Sheets.Add command and simply append the new rows to the existing sheet.

Why are rows from my second source sheet overwriting the first one?

This happens if you do not dynamically calculate the next empty row for each paste operation. Always use a command like 'NextRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1' immediately before pasting data from Mreq, so it starts exactly where the Main_Data rows ended.

Can I copy the headers to every new worksheet automatically?

Yes. When your loop creates a new worksheet, you should immediately copy row 1 (or your designated header row) from the source sheet and paste it into row 1 of the newly created sheet before proceeding to find and paste the matching data rows.

Excel & Data Problems