VBA & Macro Problems

Excel VBA Macro to Copy Rows Between Worksheets Based on X Values

Natalie TaylorNatalie Taylor Nov 20, 2026 869 views

Question details

Copy rows from the ALL worksheet to destination worksheets according to X values in columns G through J.

Copy Excel Rows Between Worksheets Based on X Values with VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A workbook has an ALL source sheet and two destination sheets, US-FR and ES-PT, with headers in row 1.
Observed behavior
Rows with X in column G or H should go to US-FR; rows with X in column I or J should go to ES-PT. Output should start below the headers and avoid variable-name errors.
Before you start

Open the workbook in desktop Excel and confirm that its sheets are named ALL, US-FR, and ES-PT, with headers in row 1. Save it as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As, and make sure columns G through J contain text values rather than cell errors.

Solution 1Recommended

Use one VBA macro to copy matching rows to both sheets

Use this macro to check columns G through J in one pass and append matching rows below existing destination data.

A source row is copied once to US-FR if G or H contains X, and once to ES-PT if I or J contains X. A row can be copied to both sheets. The comparison ignores capitalization and surrounding spaces.

1
Open the VBA editor

In desktop Excel, open the workbook, then select Developer > Visual Basic. In the editor, select the workbook in Project Explorer and choose Insert > Module.

2
Paste the macro

Paste this code into the module: Option Explicit Sub CopyMatchingRows() Dim src As Worksheet, usfr As Worksheet, espt As Worksheet Dim lastRow As Long, r As Long, col As Long Dim nextUS As Long, nextES As Long Set src = ThisWorkbook.Worksheets("ALL") Set usfr = ThisWorkbook.Worksheets("US-FR") Set espt = ThisWorkbook.Worksheets("ES-PT") lastRow = 1 For col = 7 To 10 lastRow = Application.Max(lastRow, src.Cells(src.Rows.Count, col).End(xlUp).Row) Next col nextUS = NextOutputRow(usfr) nextES = NextOutputRow(espt) For r = 2 To lastRow If IsX(src.Cells(r, "G")) Or IsX(src.Cells(r, "H")) Then src.Rows(r).Copy Destination:=usfr.Rows(nextUS) nextUS = nextUS + 1 End If If IsX(src.Cells(r, "I")) Or IsX(src.Cells(r, "J")) Then src.Rows(r).Copy Destination:=espt.Rows(nextES) nextES = nextES + 1 End If Next r End Sub Private Function IsX(cell As Range) As Boolean IsX = (UCase$(Trim$(CStr(cell.Value))) = "X") End Function Private Function NextOutputRow(ws As Worksheet) As Long Dim lastCell As Range Set lastCell = ws.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False) If lastCell Is Nothing Then NextOutputRow = 2 Else NextOutputRow = Application.Max(2, lastCell.Row + 1) End If End Function

3
Run the macro

Return to Excel, select Developer > Macros, choose CopyMatchingRows, and select Run. Check US-FR and ES-PT for the copied rows.

4
Check existing results before rerunning

If you want a fresh set of results, select and clear the old output rows below row 1 on US-FR and ES-PT before running the macro again. Otherwise, the macro appends another set of matching rows.

Use one VBA macro to copy matching rows to both sheets
Check names and spelling: The code uses the sheet names exactly as shown. If adapting an existing macro, correct inconsistent variable names such as lrwo1 and lRow1; Option Explicit helps identify undeclared names.
Free Microsoft Office alternative

Review and Filter Your Workbook in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks, filter source data, and copy matching rows between sheets as you move your work to WPS. It does not repair the Excel VBA macro.

Filter worksheet data and copy matching rows between sheetsCompatible with Microsoft Excel formats such as XLSX and XLSFamiliar spreadsheet interface for an easier transitionFree and lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

What happens if a row has X in both destination groups?

The macro copies the row once to US-FR and once to ES-PT. Multiple X values within the same group do not create extra copies on that destination sheet.

Does the macro overwrite the destination headers?

No. It starts output at row 2 or after the last row containing data or formulas on each destination sheet. Confirm that the headers are in row 1 before running it.

Why are duplicate rows appearing after I run the macro again?

The macro appends results rather than replacing them. Clear the previous output rows below the headers on US-FR and ES-PT before rerunning if you want a fresh result.

Why does Excel report an undeclared variable?

In the VBA editor, check the highlighted name for a typo, such as lrwo1 instead of lRow1. Declare variables consistently and keep Option Explicit at the top of the module to catch undeclared names.

Can I run this VBA macro in Excel for the web?

No. Open the macro-enabled workbook in desktop Excel to run VBA through Developer > Macros.