VBA & Macro Problems

How to Copy Excel Rows to Another Sheet Based on Criteria

Rana Garcia Sep 27, 2026 869 views

Question details

The user wants to automatically copy specific rows from one Excel sheet to another based on a specific column's value.

How to Copy Excel Rows to Another Sheet When Criteria Are Met

Product: Excel
Device & OS: not provided
Scenario: Synchronizing data between worksheets where rows from Sheet 1 need to be copied to Sheet 2 only if a specific condition (e.g., Column N equals 'Y') is met.
Observed behavior: The user needs a way to filter, extract, and duplicate the matching rows dynamically while keeping the original dataset intact and preserving all related record information.

Before you start

Before running any VBA macros, ensure you save a backup copy of your workbook, as macro actions cannot be undone using the standard Undo feature.

Solution 1Recommended

Use a VBA Macro for Automatic Row Copying

A VBA macro can automatically check a specific column (e.g., Column N) and copy the entire row to a destination sheet whenever the criteria are met.

By utilizing a Worksheet_Change event, the macro can trigger automatically whenever data is modified. To prevent duplicate entries, the script should clear the destination sheet before copying the matching rows over.

Open the VBA Editor

Press ALT + F11 in your Excel workbook to open the Microsoft Visual Basic for Applications window.

Insert a New Module

Click 'Insert' in the top menu and select 'Module' to create a blank script space for your copying macro.

Write the Copying Code

Enter a script that first clears Sheet 2, then loops through the used range of Sheet 1. For each row, check if the value in Column N equals 'Y'. If it does, copy the entire row to the next empty row on Sheet 2.

Automate with a Change Event

To make it update automatically, double-click 'Sheet1' in the VBA Project pane and place a call to your new macro inside the Worksheet_Change event.

Save as Macro-Enabled

Go to File > Save As, and ensure you save your document as an Excel Macro-Enabled Workbook (.xlsm) so the code functions properly.

Use a VBA Macro for Automatic Row Copying

Preventing Duplicates: Always include a command to clear the destination sheet's data (e.g., Sheets("Sheet2").Cells.ClearContents) at the beginning of your macro to ensure you always have a fresh, synchronized list.

Solution 2

Use the Advanced Filter Feature

If you prefer not to use VBA code, Excel's Advanced Filter can extract rows meeting your criteria to another sheet, though it requires manual refreshing.

Automate Data Sync Easily with WPS Spreadsheet

WPS Office provides robust, out-of-the-box support for VBA macros and advanced filtering, allowing you to automate tasks like copying rows based on criteria seamlessly.

1. Open your Workbook in WPS: Launch WPS Spreadsheet and open your data file.
2. Access the Developer Tools: Go to the Developer tab on the ribbon and click 'VBA Editor'.
3. Apply the Macro: Paste your row-copying script into a new module and save.
4. Run and Sync: Run the macro to instantly copy all matching rows to your new sheet.

Full support for running and editing Excel VBA macros and scripts.100% format compatibility with Microsoft Excel (.xlsx, .xlsm).Lightweight performance handling large datasets without freezing.Free to use with a familiar, user-friendly interface.

Frequently Asked Questions

Can I automatically copy rows without using VBA?

Yes, you can use the Power Query feature to filter and load data into a new sheet, which can be refreshed with a single click. In newer versions of Excel, you can also use the dynamic array function =FILTER() to pull data dynamically without macros.

Why isn't my Worksheet_Change macro triggering?

Ensure that macros are enabled in your Trust Center settings. Additionally, check that Application.EnableEvents hasn't been accidentally turned off (set to False) by a previously interrupted script.

How do I stop the macro from duplicating rows if I run it twice?

The recommended approach includes a step to clear the destination sheet before copying data over. By adding a line like 'Sheets("Destination").UsedRange.Clear' at the start of your macro, you prevent rows from being stacked repeatedly.

Excel & Data Problems