Copy an entire row from 1 worksheet to another

Question

I had some code sent below but cant get it to work.

Sub mybus()
    Dim x As Long

    x = 2

    'start the loop
    Do While Cells(x, 1) <> ""
        'look for data with "bus"
        If Cells(x, 1).Value = "bus" Then
            'copy the entire row if it contains bus
            Workbooks("book1").Worksheets("Sheet1").Rows(x).Copy
            'Go to sheet 2 activate it, we want the data here
            Workbooks("book1").Worksheets("Sheet2").Activate
            'Find the first empty row in sheet2
            erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            'paste the data here
            ActiveSheet.Paste Destination:=Worksheets("sheet2").Rows(erow)
        End If
        'go to sheet1 again and activate it
        Worksheets("Sheet1").Activate
        x = x + 1

    Loop
End Sub

Don't copy/paste but to answer, you need `Destination:=Worksheets("sheet2").Rows(erow+1)` — findwindow, Aug 19 '15 at 22:15
possible duplicate of [How to copy rows from one excel sheet to another and create duplicates using VBA?](http://stackoverflow.com/questions/12837297/how-to-copy-rows-from-one-excel-sheet-to-another-and-create-duplicates-using-vba) — , Aug 19 '15 at 22:24
@findwindow - `erow` is already sequenced to a new blank row with the [Range.Offset property](https://msdn.microsoft.com/en-us/library/office/ff840060.aspx). — , Aug 19 '15 at 22:25
You know @Jeeped, not everyone is a codewarrior like you who can read code UNFORMATTED as it was like you can =P Btw, tangent, but wanna do me a favor and solve [this](http://stackoverflow.com/questions/32012834/delete-excel-file) for me please? — findwindow, Aug 19 '15 at 22:29

score 2 · Answer 1 · edited May 23 '17 at 12:14

2

Avoid using Range .Activate method and Worksheet.Activate method altogether. You only need to specify the first cell in a multi-cell paste.

Sub mybus()
    Dim x As Long, erow As Long

    x = 2

    With Workbooks("book1").Worksheets("Sheet2")
        erow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End With

    With Workbooks("book1").Worksheets("Sheet1")
        Do While Cells(x, 1) <> ""
            'look for data with "bus"
            If Cells(x, 1).Value = "bus" Then
                'copy the entire row if it contains bus to Sheet2's erow
                .Rows(x).Copy _
                   Destination:=.Parent.Worksheets("sheet2").Cells(erow, 1)
                'sequence erow to a new blank row
                erow = erow + 1
            End If
            x = x + 1
        Loop
    End With

End Sub

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

edited May 23 '17 at 12:14

Community

1
1

answered Aug 19 '15 at 22:20

I was close =P I knew it had to do with incrementing erow. Edit: I am surprised you kept copy/paste. Why not just set row to row? Isn't that faster? – findwindow Aug 19 '15 at 22:31
1

You never know when bringing along formatting is essential and .Value transfer won't do that. If an OP uses copy, then I stick with it; if it is a Copy, Paste Special, Values then I move to value transfer. – Aug 19 '15 at 22:40
Teach me to be like you~ – findwindow Aug 19 '15 at 22:42
Lately, I've been playing around with `erow = Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Row` which seems to give consistent results in getting the next blank row. Make the code a little shorter but you will probably have to explain it a hundred times in a hundred questions. – Aug 19 '15 at 23:04
Can you explain it to me =P I get it until (2).row. Before that, you get the last row so what's the 2 doing? – findwindow Aug 20 '15 at 14:12
Well `B2:C3` has 4 cells right? What happens if you ask for a cell index that is larger than 4? Try this - `Dim i As Long: For i = 1 To 10: Debug.Print Range("B2:C3")(i).Address(0, 0): Next i`. – Aug 20 '15 at 14:56
You go over but still can't tell what 2 is doing. Edit: I don't know what second index of a cell means XD – findwindow Aug 20 '15 at 15:04
The `Sheet2.Cells(Rows.Count, 1).End(xlUp)` is a single cell. The `(2)` is saying the next cell which happens to be the one directly below it. The `.Row` is the row number of that cell. And that is why I don't use it in code posted here because it requires too much of an explanation. – Aug 20 '15 at 15:07
Ahh see, to me, it makes more logical sense to be (1) like x=x+1 but I guess with index, 1 is itself XD – findwindow Aug 20 '15 at 15:08
Hmm when do you use an index? I mean, how is using second index superior than +1? If you're worried about explaining, +1 makes far more sense =P – findwindow Aug 20 '15 at 15:11
Actually to me, the more verbose `.Offset(1, 0)` makes more sense but it is all a matter of programming preferences. I try to use `vbNullString` instead of `""` even though it takes more typing. Some say that `vbNullString` is faster but if that minute amount of speed is important then why are you in VBA to begin with? – Aug 20 '15 at 15:16
Oh ok. So it's more just how people think XD Thanks! – findwindow Aug 20 '15 at 15:17
Well, you can't C++ in Excel XD – findwindow Aug 20 '15 at 15:19

Copy an entire row from 1 worksheet to another

1 Answers1