Count distinct of values in one column based on name in separate?

Question

I have a master worksheet where I need to split column L data (agent names) into their own worksheets. I'm unable to assign destination worksheets based on an invalid qualifier error on this line:

If row_Data.Value = "Mike" Then

rest of code

these blocks of if statements are just to set the correct destination worksheet when column L = a name I specify.

Rest of code:

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

    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)
        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 = 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
    Next

End Sub

Only thing I thought of trying was referencing the workbook in the line by appending a wb.row_Data.value but that didn't work

edit:

so my data is overwriting without combining data.

here's my master file:

Now Mike's first row does transfer to dest worksheet:

but then when his name appears next in master worksheet, it overwrites row 2 for mikes dest worksheet

edit_2: so after I run this code, Mike's worksheet looks like:

Answer 1

• row_Data is Long variable. row_Data.Value should be Data(row_Data, 1)

        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.Cells(row_Wb, col_Data) = Data(row_Data, col_Data)
        Next

---

Update:

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 destSht As Worksheet
    Dim row_index_x As Long
    Set wb = ThisWorkbook
    With ThisWorkbook.Sheets("Master Line List - All Data")
        Data = .Range("Q3", .Range("A" & .Rows.Count).End(xlUp))
    End With
    Application.ScreenUpdating = False
    For Each sht In ThisWorkbook.Sheets
        If sht.Index <> 1 Then
            sht.Rows("3:" & sht.Range("A" & sht.Rows.Count).End(xlUp).Row).ClearContents
        End If
    Next
    For row_Data = 1 To UBound(Data)
        If Data(row_Data, 1) <> "" Then
            Agent_Name = Data(row_Data, 12)
            If InStr(1, "Mike|William|Dan|Kevin", Agent_Name, vbTextCompare) = 0 Then
                Set desSht = wb.Worksheets(Agent_Name)
                row_Wb = desSht.Cells(desSht.Rows.Count, "A").End(xlUp).Row + 1
                For col_Data = 1 To UBound(Data, 2)
                    desSht.Cells(row_Wb, col_Data) = Data(row_Data, col_Data)
                Next
            End If
        End If
    Next
End Sub