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
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]567782[/TD]
[TD]Johnson[/TD]
[TD]ann[/TD]
[TD]ZMMy6[/TD]
[TD]HiTG8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]236821[/TD]
[TD]jones[/TD]
[TD]fred[/TD]
[TD]id9s78[/TD]
[TD]PP9cg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]399722[/TD]
[TD]smith[/TD]
[TD]George[/TD]
[TD]Ci88s[/TD]
[TD]pl00La[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Tab 2 (through 6 are formatted the same with different data)
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]anderson[/TD]
[TD]cheryl[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]Fred[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]jordan[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]JOHNSON[/TD]
[TD]Ann[/TD]
[TD]97[/TD]
[/TR]
[TR]
[TD]jones[/TD]
[TD]fredricka[/TD]
[TD]21[/TD]
[/TR]
</tbody>[/TABLE]

 

[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