Excel copy whole row to other sheet

Question

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!

What's this? `Range("Cells(y, 1):Cells(y,31)").Copy`? First, you need to put the worksheet before `Range()` and `Cells()`. Also, why the quotes inside?? Do you mean `Worksheets("Sheet2").Range(Worksheets("Sheet2").Cells(y,1),Worksheets("Sheet2").Cells(y,31)).Copy`? — BruceWayne, Oct 07 '16 at 15:27
Yes, thats what I mean! lol will that work? copy the range from (y,1) to (y, 31) to new sheet3 ?! — Jimmy Huang, Oct 07 '16 at 15:31
@ScottCraner this line have the problem, `Range("Cells(y, 1):Cells(y,31)").Copy` Also, if I change my line to what @BruceWayne suggest, would it work?! Cheers! — Jimmy Huang, Oct 07 '16 at 15:35

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

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.

edited May 23 '17 at 12:15

Community

1
1

answered Oct 07 '16 at 15:40

BruceWayne

22,923
15
65
110

Cheers mate! There's an error on this line `Sheets("Sheet3").Range("A" & nextRow).EntireRow.Paste` The error is "Object doesn't support this property or method" – Jimmy Huang Oct 07 '16 at 15:57
@JimmyHuang change it to `Sheets("Sheet3").Range("A" & nextRow).PasteSpecial` – Scott Craner Oct 07 '16 at 16:00
1

Or move the nextrow line up to be the first line Under the If statement and use this `.Range(.Cells(y, 1), .Cells(y, 31)).Copy Sheets("Sheet3").Range("A" & nextRow)` in place of the two remaining lines. – Scott Craner Oct 07 '16 at 16:02
@ScottCraner Thank you a lot mate! But I found it night become an infinite loop ? – Jimmy Huang Oct 07 '16 at 16:31
@ScottCraner - Thanks, I updated the code to reflect that comment. – BruceWayne Oct 07 '16 at 16:44
@JimmyHuang - What do you mean? How could it become an infinite loop? – BruceWayne Oct 07 '16 at 16:45
@BruceWayne Sorry, my bad, just it takes too long to finish. – Jimmy Huang Oct 08 '16 at 00:17
@JimmyHuang you could always throw `Application.ScreenUpdating = False` and manual calculation at the start to try and speed things up – BruceWayne Oct 08 '16 at 18:03
@BruceWayne Sorry, where should I put this code at? And what's this used for ? – Jimmy Huang Oct 08 '16 at 18:08
@BruceWayne I put it below `Sub test()`, but when I try to run the code, the error message shows up, **Can't execute code in break mode** ... – Jimmy Huang Oct 08 '16 at 18:13
@ScottCraner hmmmm.. I find that somehow, `If InStr(1, CStr(site_name), .Cells(y, 30)) >= 1 Then`this line doesn' work, and type of `.Cells(y, 30)` is string, could you enlighten me on this .. – Jimmy Huang Oct 08 '16 at 18:43
@JimmyHuang ask another question with details of the data and what you have tried. Comment of an answer is not the place to ask a new question. – Scott Craner Oct 08 '16 at 18:45

Excel copy whole row to other sheet

1 Answers1