Find and Copy

I need some help getting a macros created. (Hopefully it is somewhat basic/easy for someone who is not a beginner like me. I will include the excel file as a template.

I have two tabs, TEAMS and ROSTER. I need to be able to match a name from ROSTER (column B) to a name in TEAMS (unknown row/column) and put the assigned supervisor (row 1) into the ROSTER tab next to the employees they belong to.

I have included comments on the excel file I am attaching. If you need further explanation, feel free to email me.

Thank you ahead of time for any time and effort you all may put into this process.

What happens if you have two employees with the same first names? You may want to re-think your design.

Put this code into a command button or some other object. It will prompt for the Employee name, find it and then place the Supervisor name in the roster worksheet.

This code assumes that the data structure you provided will remain constant.

Sub Names()

Dim Supervisor As String
Dim Employee As String
Dim Col As Integer
Dim Rw As Integer

Worksheets("Teams").Activate

Employee = InputBox("Enter Employee Name")

Cells.Find(What:=Employee, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    
Col = ActiveCell.Column

Supervisor = Cells(1, Col).Value

Worksheets("Roster").Activate

Cells.Find(What:=Employee, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

Rw = ActiveCell.Row

Cells(Rw, 1).Value = Supervisor
    
End Sub

That is incredible! Thank you!

Also, concerning your advice to rethink my design. If I had the employees listed as "Lastname, Firstname" I am sure that would help reduce the confusion.

Not sure how to word this next question, but I will try.

Instead of me having to "tell excel who to look for", can the macro automatically put the manager next to each name in the ROSTER tab without any kind of prompt/question box?, if there is a name? (sorry if that doesn't make good sense)

Excel can do it any number of ways. The hardest part of solving a problem is understanding what the problem is.

Are you saying that anytime you add a new name to either worksheet that you want the program to update the Manager - Employee relationships on Worksheet Roster?

One way to approach that would be to put all the names in a list or array and then search through each name. The input box would be replaced and you would use some sort of loop to cycle through all the code until all the names had been updated.

As far as design, I'd use something that will never change or be duplicated like an employee ID and Manager ID. First and Last names will help but what if a person gets married and their last name changes or you hire two people with the same first and last name?