Compare and Match 2 cols in 2 wkshts and return data

[TheOneNOnlyQ]

I have one workbook with 6 tabs.
On tab 1 I have a last name, first name, some other columns with stuff in them and 5 empty columns.
On tabs 2 - 6, I have a last name col, first name col and numbered data in the 3rd col.
I want to fill the 5 empty cols in tab 1 with the numbered data from tabs 2 - 6 IF the first AND last names match (capitalizations may be off) tab 1.
(The blank columns in tab1 correspond to weeks. Each tab, 2 - 6, corresponds to a weeks data. So tab2's numbered data would go into the first blank col, tab3's data would go into the second blank col...)
**Ideally, if a persons name is in any of the tabs 2 - 6 but not on tab 1, their first, last and numbered data would be added at the bottom of the data in tab1 (but that's a wishlist item)**

Tab 1

567782	Johnson	ann	ZMMy6	HiTG8
236821	jones	fred	id9s78	PP9cg
399722	smith	George	Ci88s	pl00La

<tbody>
</tbody>

Tab 2 (through 6 are formatted the same with different data)

anderson	cheryl	15
Jones	Fred	6
Smith	jordan	7
JOHNSON	Ann	97
jones	fredricka	21

<tbody>
</tbody>

 

[onlyadrafter]

Hello,

does this code work as expected?

Sub GET_DATA()
    With Sheets(1)
        For MY_ROWS = 1 To .Range("A" & Rows.Count).End(xlUp).Row
            MY_NAME = UCase(.Range("B" & MY_ROWS).Value & UCase(.Range("C" & MY_ROWS).Value))
            For MY_SHEETS = 2 To 6
                With Sheets(MY_SHEETS)
                    For MY_NEW_ROWS = 1 To .Range("A" & Rows.Count).End(xlUp).Row
                        MY_NEW_NAME = UCase(.Range("A" & MY_NEW_ROWS).Value & UCase(.Range("B" & MY_NEW_ROWS).Value))
                        If MY_NAME = MY_NEW_NAME Then
                            Sheets(1).Cells(MY_ROWS, 4 + MY_SHEETS).Value = .Range("C" & MY_NEW_ROWS).Value
                        End If
                    Next MY_NEW_ROWS
                End With
            Next MY_SHEETS
        Next MY_ROWS
    End With
End Sub

 

[onlyadrafter]

Hello,
and with your wish list

Sub GET_DATA()
    For MY_SHEETS = 2 To 6
        With Sheets(MY_SHEETS)
            For MY_NEW_ROWS = 1 To .Range("A" & Rows.Count).End(xlUp).Row
                MY_NEW_NAME = UCase(.Range("A" & MY_NEW_ROWS).Value & UCase(.Range("B" & MY_NEW_ROWS).Value))
                With Sheets(1)
                    For MY_ROWS = 1 To .Range("B" & Rows.Count).End(xlUp).Row
                        MY_NAME = UCase(.Range("B" & MY_ROWS).Value & UCase(.Range("C" & MY_ROWS).Value))
                            If MY_NAME = MY_NEW_NAME Then GoTo FOUND
                    Next MY_ROWS
                    .Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets(MY_SHEETS).Range("A" & MY_NEW_ROWS).Value
                    .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets(MY_SHEETS).Range("B" & MY_NEW_ROWS).Value
                End With
FOUND:
            Next MY_NEW_ROWS
        End With
    Next MY_SHEETS
    With Sheets(1)
        For MY_ROWS = 1 To .Range("B" & Rows.Count).End(xlUp).Row
            MY_NAME = UCase(.Range("B" & MY_ROWS).Value & UCase(.Range("C" & MY_ROWS).Value))
            For MY_SHEETS = 2 To 6
                With Sheets(MY_SHEETS)
                    For MY_NEW_ROWS = 1 To .Range("A" & Rows.Count).End(xlUp).Row
                        MY_NEW_NAME = UCase(.Range("A" & MY_NEW_ROWS).Value & UCase(.Range("B" & MY_NEW_ROWS).Value))
                        If MY_NAME = MY_NEW_NAME Then
                            Sheets(1).Cells(MY_ROWS, 4 + MY_SHEETS).Value = .Range("C" & MY_NEW_ROWS).Value
                        End If
                    Next MY_NEW_ROWS
                End With
            Next MY_SHEETS
        Next MY_ROWS
    End With
End Sub