VBA & Macro Problems

How to Copy Excel Rows Based on Specific Text Using a VBA Macro

WPS Content Manager Oct 7, 2026 869 views

Question details

The user needs an Excel VBA macro to search a column for a specific word, copy data from specific columns of the matched rows to rearranged columns on another worksheet, and automatically populate dropdown fields while preventing duplicate entries.

How to Copy Excel Rows Containing Specific Text to Another Worksheet via VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data extraction by transferring specific columns of conditionally matched rows to a new worksheet without overwriting data validation rules.
Observed behavior: The macro needs to identify rows containing "Tripped" in column H, map columns A, E, and H to C, A, and F on the destination sheet, assign dropdown values, and avoid copying the same row multiple times on subsequent runs.

Before you start

Save your Excel file as a Macro-Enabled Workbook (.xlsm) and ensure the Developer tab is enabled in your ribbon before writing or running the script.

Solution 1Recommended

Create a VBA Macro with a Loop to Copy Specific Columns

Use a VBA `For` loop to iterate through the source column, check for the specified text, and map individual cell values to the destination worksheet.

Instead of copying the entire row, explicitly mapping source cells to destination cells allows you to rearrange the columns during the transfer. This method also allows you to populate specific fields directly via the script.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

Click 'Insert' from the top menu bar, then select 'Module' to create a blank script window.

Write the Loop Logic

Define your variables for worksheets and rows. Use a `For i = 1 To LastRow` loop to check `If ws1.Range("H" & i).Value = "Tripped" Then`. Inside the `If` statement, assign values: `ws2.Range("C" & nextRow).Value = ws1.Range("A" & i).Value`.

Populate Target Dropdowns

Directly assign static values to destination cells within the same `If` block to simulate populating dropdown fields, for example: `ws2.Range("D" & nextRow).Value = "WEST"`.

Run the Macro

Close the VBA editor and return to Excel. Go to the Developer tab, click 'Macros', select your new macro, and click 'Run'.

Create a VBA Macro with a Loop to Copy Specific Columns

Preserving Data Validation: By assigning values directly (e.g., `Cell.Value = Cell.Value`) instead of using the `.Copy` method, you ensure that the destination worksheet's existing data validation rules and dropdown lists remain intact.

Solution 2

Add a Unique-Row Check to Prevent Duplicates

Modify your VBA script to verify if a row has already been copied to the destination worksheet before executing the transfer.

Use WPS Spreadsheet for VBA Macros

Automate Data Transfer Using WPS Spreadsheet Macros

WPS Spreadsheet provides excellent support for VBA macros, allowing you to run, edit, and create complex scripts to automate workflows, copy rows, and manage data without any friction.

1. Open Your Workbook: Launch WPS Spreadsheet and open your Macro-Enabled Workbook containing the data.
2. Access the Developer Tools: Click on the 'Developer' tab in the top ribbon, then select 'VBA Editor' to launch the coding environment.
3. Add Your Script: Go to 'Insert' > 'Module' and paste your VBA loop script to find and copy the specific rows.
4. Execute the Macro: Return to your spreadsheet, click 'Macros' in the Developer tab, select your script, and click 'Run'.

Fully compatible with Microsoft Excel macro formats (.xlsm)Built-in VBA editor for seamless script creation and debuggingFree and lightweight Office suite with a familiar user interfaceRobust data validation and dropdown list management

microsoft office alternative - wps office

Frequently Asked Questions

How do I enable the Developer tab to access the VBA Editor?

To enable the Developer tab in Excel, go to File > Options > Customize Ribbon. In the right pane, check the box next to 'Developer' and click OK. The tab will now appear in your main menu ribbon.

Can I copy rows to a different workbook instead of just another worksheet?

Yes. In your VBA script, you must define variables for both workbooks. For example, use `Set wbTarget = Workbooks.Open("C:\Path\To\File.xlsx")` and reference `wbTarget.Sheets("Test2")` when assigning the destination values.

Why is my VBA macro overwriting my data validation dropdowns?

This happens if you use the standard `Range.Copy` method, which copies formatting and data validation rules along with the value. To prevent this, assign values directly using `DestinationCell.Value = SourceCell.Value` or use `Range.PasteSpecial xlPasteValues`.

How do I make the macro run automatically when a cell value changes?

You can use the `Worksheet_Change` event. Right-click the source sheet tab, select 'View Code', and place your logic inside a `Private Sub Worksheet_Change(ByVal Target As Range)` block, ensuring it checks if the changed cell intersects with your target column.

Excel & Data Problems