Excel VBA Macro to Copy Rows Between Worksheets Based on X Values
Question details
Copy rows from the ALL worksheet to destination worksheets according to X values in columns G through J.

- 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.
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.
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.
In desktop Excel, open the workbook, then select Developer > Visual Basic. In the editor, select the workbook in Project Explorer and choose Insert > Module.
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
Return to Excel, select Developer > Macros, choose CopyMatchingRows, and select Run. Check US-FR and ES-PT for the copied rows.
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.

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.

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.




