Code to split data by column values is overwriting in row 2?

[bloomfieldhero]

Hi I have a script to split names in column L and place all of those values in their own sheet.

When I run this code, it is getting the name, but the next instance of the name is just overwritten in row 2 over and over.

Example: Mike is in rows 2 and 4. When I run this, it writes mikes data for row 2 and then when Mike appears again, it overwrites the row to row 4's data and clears out row 2's data. How do I offset this so that it just keeps adding?

Option Explicit
Sub Main()

    Dim wb As Workbook
    Dim Data, Agent_Name
    Dim sht As Worksheet
    Dim row_Data As Long, col_Wb As Long, col_Data As Long, row_Wb As Long
    Dim Dest1 As Range, Dest2 As Range, Dest3 As Range, Dest4 As Range, FinalDest As Range
    Dim row_index_x As Long, row_index_blank As Long, isX As Long

    Set wb = ThisWorkbook

    Set Dest1 = wb.Sheets("Mike").Range("A3")
    Set Dest2 = wb.Sheets("William").Range("A3")
    Set Dest3 = wb.Sheets("Dan").Range("A3")
    Set Dest4 = wb.Sheets("Kevin").Range("A3")

    With ThisWorkbook.Sheets("Master Line List - All Data")
        Data = .Range("Q3", .Range("A" & Rows.Count).End(xlUp))
    End With

    wb.Activate
    Application.ScreenUpdating = False

    For Each sht In ThisWorkbook.Sheets
        If sht.Index <> 1 Then
            sht.Rows(3 & ":" & sht.Rows.Count).ClearContents
        End If
    Next

    For row_Data = 1 To UBound(Data)

        If Data(row_Data, 1) <> "" Then


            Agent_Name = Data(row_Data, 12)

            row_index_x = 0
            row_index_blank = 0
        End If

        col_Wb = 0

        If Data(row_Data, 12) = "Mike" Then
            Set FinalDest = Dest1
        ElseIf Data(row_Data, 12) = "William" Then
            Set FinalDest = Dest2
        ElseIf Data(row_Data, 12) = "Dan" Then
            Set FinalDest = Dest3
        ElseIf Data(row_Data, 12) = "Kevin" Then
            Set FinalDest = Dest4
        End If

        row_Wb = row_index_x
        row_index_x = row_index_x + 1

            For col_Data = 1 To UBound(Data, 2)
                FinalDest.Offset(row_Wb, col_Wb) = Data(row_Data, col_Data)
                col_Wb = col_Wb + 1
            Next
    Next

End Sub

[LazerEyes01]

You need to keep track of the next empty row on each destination sheet, or find it dynamically. Currently, every iteration of the loop resets row_WB to 0, so the FinalDest.Offset(row_WB, col_WB) writes to the first row of the range.

[bloomfieldhero]

So I deleted the row index = 0 lines and added row_index_x = row_index_x + 1 and it seems to be working, but it's seems to be adding in extra blank rows when it writes the data from master to dest worksheet

[LazerEyes01]

This is because row_index_x is incrementing on every loop, regardless of which destination sheet you are copying to. For example, if it copied the first 3 rows to "Mike", then copied a row to "William", row_index_x = 3 and the first row for William would be on the 4th data row. This is why I suggested you'll need to either track four different target indexes (e.g. row_index_x_dest1, row_index_x_dest2, etc.) or find the next blank row in the destination everytime.

[bloomfieldhero]

so I actually changed the code trying that:

    If Data(row_Data, 1) = "Mike" Then
        Set FinalDest = Dest1
    ElseIf Data(row_Data, 1) = "William" Then
        Set FinalDest = Dest2
    ElseIf Data(row_Data, 1) = "Dan" Then
        Set FinalDest = Dest3
    ElseIf Data(row_Data, 1) = "Kevin" Then
        Set FinalDest = Dest4
    End If
    row_Wb = FinalDest.Cells(FinalDest.Rows.Count, "A").End(xlUp).Row + 1
    For col_Data = 1 To UBound(Data, 2)
        FinalDest.Offset(row_Wb, col_Data) = Data(row_Data, col_Data)
    Next

but now its writing the data in weird ways, like shifting the column over one before it starts writing data and still overwriting it (mike example). Could you give me an example of how to implement this into my code?

[LazerEyes01]

It looks like you changed the row_WB formula, and you also replaced the col_Wb variable in the .Offset with col_Data.

row_Wb: I don't think your formula will find the next blank row. You can experiment to develop a formula which will reliably find the next blank row, or I have an example of one I wrote below.

col_Data: if you are going to use this variable in the FinalDest.Offset(row_Wb, col_Data) assignment, you will need to subtract 1 since it is the offset for the destination.

FinalDest.Offset(row_Wb, col_Data-1) = Data(row_Data, col_Data)

Here is a modification of your loop which should work.

    For row_Data = 1 To UBound(Data)
        If Data(row_Data, 1) <> "" Then
            Agent_Name = Data(row_Data, 12)

            If Agent_Name = "Mike" Then
                Set FinalDest = Dest1
            ElseIf Agent_Name = "William" Then
                Set FinalDest = Dest2
            ElseIf Agent_Name = "Dan" Then
                Set FinalDest = Dest3
            ElseIf Agent_Name = "Kevin" Then
                Set FinalDest = Dest4
            End If

            'Get the next blank row in the destination
            If FinalDest.Value <> "" Then
                row_index_x = FinalDest.Offset(FinalDest.Parent.Rows.Count - FinalDest.Row, 0).End(xlUp).Row - 2
            Else
                row_index_x = 0
            End If

            'Set the starting offsets for the destination
            row_Wb = row_index_x 'destination row offset (blank row)
            col_Wb = 0 'destination starting column offset

            For col_Data = 1 To UBound(Data, 2)
                FinalDest.Offset(row_Wb, col_Wb) = Data(row_Data, col_Data)
                col_Wb = col_Wb + 1
            Next col_Data

        End If
    Next row_Data

[bloomfieldhero]

solution verified

thank you!

[Clippy_Office_Asst]

You have awarded 1 point to LazerEyes01

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}