Copying data from one row to the next available row in the next sheet, loop

Question

I have practically no experience with coding in general, and have been watching videos on youtube to try and write a few codes. I have a "Master" sheet with a bunch of data, and I need to take that data and transfer it to each consecutive sheet based on the date. So all data with nov 17 to sheet 3 and all data from nov 18 to sheet four and so on. The issue I encounter is when I try to cycle through to the next date.

Option Explicit

Sub Copypaste()

                        'Sets a as  variable for number of rows from master sheet
    a = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a      'Loops from i=2 to end
        k = 3           'sheet number
        J = 43048       'date
        If Worksheets("Master").Cells(i, 1).Value = J Then
            Worksheets("Master").Rows(i).Copy
            Worksheets(k).Activate
                        'counts rows in sheet pasting to
            b = Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
                        'pasts to next blank row
            Worksheets(k).Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Master").Activate
        Else
            k = k + 1
            J = J + 1
        End If
    Next

End Sub

Possible duplicate of [Excel VBA Copying to Blank Row](https://stackoverflow.com/questions/16056511/excel-vba-copying-to-blank-row) — ashleedawg, Jun 19 '18 at 15:31
Just wondering - does it work at all, as you are having `Option Explicit` and `a, J, k, b` are not declared? Are they public variables in some other module? — Vityata, Jun 19 '18 at 15:34
Yes, it works to the extent of running through on date, so anything with the start date nov 9 2017, and pastes all of those to the third sheet but not past that. — D.Taylor, Jun 21 '18 at 20:58

Vityata · Answer 1 · 2018-06-19T15:36:57.370

You have to put the date out of the loop and then to increment by one.

Sub Copypaste()

                        'Sets a as  variable for number of rows from master sheet
    a = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
    J = 43048       'date

    For i = 2 To a      'Loops from i=2 to end
        k = 3           'sheet number
        If Worksheets("Master").Cells(i, 1).Value = J Then
            Worksheets("Master").Rows(i).Copy
            Worksheets(k).Activate
                        'counts rows in sheet pasting to
            b = Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
                        'pasts to next blank row
            Worksheets(k).Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Master").Activate
        Else
            k = k + 1
            J = J + 1
        End If
    Next

End Sub

Thus the code above should work. If you try to declare the worksheets as variables and avoid Select and Activate, the code would go faster - How to avoid using Select in Excel VBA

Thus, it would look like this:

Sub Copypaste()
    a = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a
        k = 3
        J = 43048
        With Worksheets("Master")
            If .Cells(i, 1) = J Then
                .Rows(i).Copy
                b = Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
                Worksheets(k).Cells(b + 1, 1).Paste
            Else
                k = k + 1
                J = J + 1
            End If
        End With
    Next

End Sub

As a third step, you may consider writing normal variable names. E.g. instead of a write rowsCountMaster and instead of J write currentDate.

Hey, THanks to getting back to me. I see what you mean about taking the variables out of the loop, that makes a lot more sense. However now I am running into an error with variable a, "Run-time error'9': Subscript out of range" — D.Taylor, Jun 21 '18 at 20:55

Copying data from one row to the next available row in the next sheet, loop

1 Answers1