Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Long
    Dim sProfileName As String

    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    sProfileName = Range("A1").Value

    n = 0
    On Error Resume Next
    n = Worksheets(sProfileName).Index
    On Error GoTo 0
    
    If n = 0 Then
        Call MsgBox("Worksheet " & sProfileName & " not in workbook", vbExclamation + vbOKOnly, "Profile Finder")
        Exit Sub
    End If
    
    Application.EnableEvents = False
    Worksheets(sProfileName).Range("B2:B10").Copy Me.Range("B2:B10")
    Application.EnableEvents = True
End Sub


Fairly straight forward …

Worksheeet_Change is an event handler that gets called each time the worksheet has a cell(s) changed and passed the changed range

Intersect () looks to see if A1 as the cell that changed (= Nothing is the intersection of Target and A1 is empty)

The n =0 part is just the way I check to see it the profile worksheet exists (n <> 0 means it exists)

It's important to not process any more events since otherwise the event handler keeps calling itself

The .Copy copies the cells, not necessary to activate the sheet first