Excel跨表匹配复制需求求助：匹配后整行复制至Output表

Hey there! Since you're new to Excel, let's break this down into two simple, reliable methods to get your matched rows copied over to the Output sheet—one that uses basic Excel features (no coding needed) and another for automated repeats with VBA.

This is the easiest route for beginners, no programming required:

• First, set up your criteria range: On Sheet2, pick a blank column (say, Column B). In cell B1, type the exact header from Sheet1's Column B (e.g., if Sheet1 B1 is "Match Value", Sheet2 B1 needs to be the same). Then copy all values from Sheet2's Column A (including duplicates) into Sheet2's Column B starting at B2.
• Next, go to Sheet1, select your entire data range (including headers). Click the Data tab in the ribbon, then find the Advanced button in the "Sort & Filter" group.
• In the Advanced Filter dialog box:
  • Select "Copy to another location".
  • For "List range", confirm it's your Sheet1 data (e.g., Sheet1!$A:$C—if you know your data ends at row 100, use Sheet1!$A$1:$C$100 for better performance).
  • For "Criteria range", select the range you set up on Sheet2 (e.g., Sheet2!$B$1:$B$50, replacing 50 with your last row of data).
  • For "Copy to", click cell A1 on the Output sheet (make sure Output's A1 is either blank or matches Sheet1's header).
• Important: Don't check "Unique records only"—you want all matching rows, including duplicates. Hit OK, and all matching rows from Sheet1 will populate the Output sheet.

If your data changes regularly and you don't want to redo the filter every time, a simple VBA macro will let you run this with one click:

• Press Alt + F11 to open the VBA Editor.
• In the left "Project Explorer" pane, right-click your workbook name, then select Insert > Module.
• Paste this code into the new module:

Sub CopyMatchedRows()
    Dim ws1 As Worksheet, ws2 As Worksheet, wsOutput As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long, lastRowOutput As Long
    Dim i As Long, j As Long
    
    ' Link to your worksheets (update names if yours are different)
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    Set wsOutput = ThisWorkbook.Sheets("Output")
    
    ' Clear existing data in Output (keeps headers if you have them)
    ' If Output has no headers, replace this line with wsOutput.Cells.Clear
    wsOutput.Range("A2:C" & wsOutput.Cells(wsOutput.Rows.Count, "A").End(xlUp).Row).Clear
    
    ' Find the last row with data in each sheet
    lastRow1 = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through every row in Sheet1
    For i = 2 To lastRow1 ' Start at 2 if you have headers; use 1 if no headers
        ' Loop through every row in Sheet2 to find matches
        For j = 2 To lastRow2 ' Same here: 2 for headers, 1 for no headers
            If ws1.Cells(i, "B").Value = ws2.Cells(j, "A").Value Then
                ' Find the next blank row in Output
                lastRowOutput = wsOutput.Cells(wsOutput.Rows.Count, "A").End(xlUp).Row + 1
                ' Copy the entire row from Sheet1 to Output
                ws1.Rows(i).Copy Destination:=wsOutput.Rows(lastRowOutput)
                ' Keep looping to catch duplicate matches in Sheet2
            End If
        Next j
    Next i
    
    MsgBox "All matching rows copied to Output sheet!", vbInformation
End Sub

• Quick adjustments: If Sheet1 or Sheet2 don't have headers, change i = 2 to i = 1 and j = 2 to j = 1 in the code.
• Run the macro: Press F5 in the VBA Editor, or go back to Excel, click the Developer tab, select Macros, pick CopyMatchedRows, and hit Run.

Quick Notes #

• Double-check that your worksheet names exactly match what's in the code (Sheet1, Sheet2, Output)—if you renamed them, update the code accordingly.
• For large datasets, the VBA method will be faster than Advanced Filter, especially with lots of duplicate values in Sheet2.

内容的提问来源于stack exchange，提问作者Abhishek Mishra