Excel将整行复制到其他工作表(Excel copy whole row to other sheet)

 任何人都可以帮我兑现这些东西！？ 我想使用Sheet1的第7列中的每个值作为参数，并将其复制到Sheet2，如果Sheet2具有此参数，则使用sheet1的第1列中的值在Sheet2的第30列中搜索，如果它符合条件，然后将sheet2中的整行复制到新的sheet3 
 
Sub test()

    ' Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet3"
    ' FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

    LastRow_Sheet1 = Worksheets("Sheet1").UsedRange.Rows.Count
    LastRow_Sheet2 = Worksheets("Sheet2").UsedRange.Rows.Count

    For x = 2 To LastRow_Sheet1

        po_number = Worksheets("Sheet1").Cells(x, 7).Value
        site_name = Worksheets("Sheet1").Cells(x, 1).Value
        Worksheets("Sheet2").Activate

        For y = 2 To LastRow_Sheet2
            If po_number <> Worksheets("Sheet1").Cells(y, 1).Value Then
                If InStr(1, CStr(site_name), Worksheets("Sheet2").Cells(y, 30)) >= 1 Then
                    Range("Cells(y, 1):Cells(y,31)").Copy
                    Sheets("Sheet3").Select
                    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
                    Cells(NextRow, 1).Select
                    ActiveSheet.Paste
                End If
            End If

        Next
    Next

End Sub
 
 正如Scott所建议的那样，我将代码改为如下，Sub test（） 
 
LastRow_Sheet1 = Worksheets("Sheet1").UsedRange.Rows.Count
LastRow_Sheet2 = Worksheets("Sheet2").UsedRange.Rows.Count

For x = 2 To 2

    po_number = Worksheets("Sheet1").Cells(x, 7).Value
    site_name = Worksheets("Sheet1").Cells(x, 1).Value

    For y = 2 To 20000
        If po_number <> Worksheets("Sheet2").Cells(y, 1).Value Then
        With Worksheets("Sheet2") ' I ASSUME THIS IS THE SHEET YOU WANT TO WORK WITH. CHANGE AS NECESSARY
            If InStr(1, CStr(site_name), .Cells(y, 30)) >= 1 Then
                Range(Cells(y, 1), Cells(y, 31)).Copy
                nextRow = Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, 1).End(xlUp).Row + 1
                Sheets("Sheet3").Range("A" & nextRow).PasteSpecial
                'You may need to change this. I am not sure what range you were wanting to paste to
            End If
          End With
        End If
    Next
Next
 
 结束子 
 
 但没有任何复制到Sheet3大声笑...而且，我想知道什么是Sheets("Sheet3").Range("A" & nextRow).PasteSpecial这一行代表什么？ 
 
 非常感谢！ 

Could any one help me to dubug this things!? I want to use each value in 7th column of Sheet1 as parameters, and copy it to Sheet2, and if Sheet2 has this parameters, then use values in 1st column of sheet1 to search in Column 30th of Sheet2, and if it meet the criteria, then copy whole row in sheet2 to new sheet3
 
Sub test()

    ' Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet3"
    ' FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

    LastRow_Sheet1 = Worksheets("Sheet1").UsedRange.Rows.Count
    LastRow_Sheet2 = Worksheets("Sheet2").UsedRange.Rows.Count

    For x = 2 To LastRow_Sheet1

        po_number = Worksheets("Sheet1").Cells(x, 7).Value
        site_name = Worksheets("Sheet1").Cells(x, 1).Value
        Worksheets("Sheet2").Activate

        For y = 2 To LastRow_Sheet2
            If po_number <> Worksheets("Sheet1").Cells(y, 1).Value Then
                If InStr(1, CStr(site_name), Worksheets("Sheet2").Cells(y, 30)) >= 1 Then
                    Range("Cells(y, 1):Cells(y,31)").Copy
                    Sheets("Sheet3").Select
                    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
                    Cells(NextRow, 1).Select
                    ActiveSheet.Paste
                End If
            End If

        Next
    Next

End Sub
 
As suggested by Scott, I change the code as below, Sub test()
 
LastRow_Sheet1 = Worksheets("Sheet1").UsedRange.Rows.Count
LastRow_Sheet2 = Worksheets("Sheet2").UsedRange.Rows.Count

For x = 2 To 2

    po_number = Worksheets("Sheet1").Cells(x, 7).Value
    site_name = Worksheets("Sheet1").Cells(x, 1).Value

    For y = 2 To 20000
        If po_number <> Worksheets("Sheet2").Cells(y, 1).Value Then
        With Worksheets("Sheet2") ' I ASSUME THIS IS THE SHEET YOU WANT TO WORK WITH. CHANGE AS NECESSARY
            If InStr(1, CStr(site_name), .Cells(y, 30)) >= 1 Then
                Range(Cells(y, 1), Cells(y, 31)).Copy
                nextRow = Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, 1).End(xlUp).Row + 1
                Sheets("Sheet3").Range("A" & nextRow).PasteSpecial
                'You may need to change this. I am not sure what range you were wanting to paste to
            End If
          End With
        End If
    Next
Next
 
End Sub
 
But nothing copy to Sheet3 lol ... And also, I am wondering what Sheets("Sheet3").Range("A" & nextRow).PasteSpecial this line stands for?
 
Many thanks!

原文：https://stackoverflow.com/questions/39920808