Getting Range.Find to work

Question

I'm working on a project for work and I need help changing this code to work with my current worksheet. In my "Registration" worksheet I have an area where I'm trying to type in the "Athlete" name and it will pull the information from the "Master List" worksheet.

Update Here is the original workbook that this coding is coming from. I'm trying to get this coding to work on my new work book because I'm using two worksheets instead of one.

Here is the old code that I have but am unable to configure it to work with this new workbook since I'm working off of two worksheets instead of one.

Sub Update()
Dim rngSearch As Range, rngFound As Range
Set rngSearch = Range("B18:B150")
Set rngFound = rngSearch.Find(What:=Range("B12").Value, LookIn:=xlValues, 
LookAt:=xlPart)

If rngFound Is Nothing Then
MsgBox "Please recheck name"
Range("B12").Value = " "
Exit Sub

End If

'Range("B12:O12").Copy
'Range("B" & rngFound.Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
    :=False, Transpose:=False
'Range("B12:O12").ClearContents
For a = 5 To 14
If Cells(12, a) = "X" Or Cells(12, a) = "X" Then
Cells(rngFound.Row, a).Value = "X"
Else
Cells(rngFound.Row, a).Value = " "
End If
Next
Range("B12:O12").ClearContents

Range("C12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,2,FALSE))"
Range("D12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,3,FALSE))"
Range("E12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,4,FALSE))"
Range("F12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,5,FALSE))"
Range("G12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,6,FALSE))"
Range("H12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,7,FALSE))"
Range("I12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,8,FALSE))"
Range("J12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,9,FALSE))"
Range("K12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,10,FALSE))"
Range("L12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,11,FALSE))"
Range("M12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,12,FALSE))"
Range("N12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,13,FALSE))"
Range("O12").Formula = "=IF($B12="" "","" "",VLOOKUP($B12,$B18:$O150,14,FALSE))"

End Sub

* Update * I was able to find a Macro that needs to be updated as well. This was hiding in a different Module.

Sub values()
'
'values Macro
'

'
    Range("B12:O12").Select
    Selection.Copy
    Range("B18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

End Sub

Here are a couple images of the new worksheets I am working on.

Update Athlete: A29:O29

"Master List"

Answer 1

In your get data file (the one with button) try remove any merge column (A&B). Do the same for "master list" As you might seen, you input name in cell A29 so you need to change B12 -> A29

Sub Update()
Dim rngSearch As Range, rngFound As Range
'Search range: Change sheets and range 
Set rngSearch = Sheets("Master list").Range("A3:O150")
' You type in cell A29 then search by cell A29, not B12
Set rngFound = rngSearch.Find(What:=Range("A29").Value, LookIn:=xlValues, LookAt:=xlPart)

If rngFound Is Nothing Then
    MsgBox "Please recheck name"
    Range("A29").Value = " "
    Exit Sub
End If
'I think this is meaningless
'Range("B12:O12").Copy
'Range("B" & rngFound.Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Range("B12:O12").ClearContents
'For a = 5 To 14
'If Cells(12, a) = "X" Or Cells(12, a) = "X" Then
'Cells(rngFound.Row, a).Value = "X"
'Else
'Cells(rngFound.Row, a).Value = " "
'End If
'Next
'Range("B12:O12").ClearContents

'You should pay attention to this as this is where your data will show: Row 29 from column B to O ( I assume that you remove the merge cell A&B)
' Source :master list
Range("B29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,2,FALSE))"
Range("C29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,3,FALSE))"
Range("D29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,4,FALSE))"
Range("E29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,5,FALSE))"
Range("F29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,6,FALSE))"
Range("G29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,7,FALSE))"
Range("H29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,8,FALSE))"
Range("I29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,9,FALSE))"
Range("J29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,10,FALSE))"
Range("K29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,11,FALSE))"
Range("L29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,12,FALSE))"
Range("M29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,13,FALSE))"
Range("N29").Formula = "=IF($A29="","",VLOOKUP($A29,'Master list'!$A3:$O150,14,FALSE))"

End Sub

update for the values sub

    Sub values()
    '
    'values Macro
    '

    ' Edit your target sheet here:
With Sheets("target sheet name")
' Avoid select, it will slow down your code
        .Range("B12:O12").Copy
        .Range("B18").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    end with
    End Sub

p/s: might be this one is all you need:

=IFERROR(VLOOKUP(A29,'Master list'!$A3:$O150,2,FALSE),"")