CODEWITHSUNDEEP
excel
Jimmy Huang
Jimmy Huang

Reputation: 49

Excel copy whole row to other sheet

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!

Upvotes: 0

Views: 108

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

If I understand correctly, I think you want this:

Sub test()

    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

        For y = 2 To LastRow_Sheet2
            If po_number <> Worksheets("Sheet1").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
                    nextRow = Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, 1).End(xlUp).Row + 1
                    .Range(.Cells(y, 1), .Cells(y, 31)).Copy Sheets("Sheet3").Range("A" & nextRow)
                End If
              End With
            End If
        Next
    Next
End Sub

As mentioned in the comments, the main problem is your line Range("Cells(y, 1):Cells(y,31)").Copy. Additionally, make sure to qualify all Range(),Cells(),Rows.Count, etc. with the worksheet you want that to run on. Also, I tried to remove all instances of .Activate/.Active. The ranges may need tweaking, so let me know if this doesn't work.

Upvotes: 1

Related Questions