You should include all your code in the book you post, not rely on previous posts

Note: The code runs when data is entered in Calc Sheet B2
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim S, FNum, FName, PC, sh As Worksheet, tgt As Range
    On Error GoTo Exits
    Application.EnableEvents = False
    If Target.Address = "$B$2" Then
        S = Target.Offset(, -1)
        PC = Target.Offset(, 3)
        FName = Target.Offset(, 1)
        FNum = Target
        
         For Each sh In Worksheets            Select Case sh.Name
            Case "NCF_Data"
                Set tgt = sh.Columns(1).Find(S, after:=sh.Cells(1, 1), searchdirection:=xlPrevious)
                tgt(2).EntireRow.Insert
                tgt(2).Resize(, 16) = Array(S, "", FNum, FName, "", "", "", "", "", "", "", "", "", "", "", PC)
            Case "FundName Rollup Mapping"
                Set tgt = sh.Cells(Rows.Count, 1).End(xlUp)(2)
                tgt.Resize(, 2) = Array(FName, FNR)
            Case "Prod Category Lookup"
                Set tgt = sh.Cells(Rows.Count, 1).End(xlUp)(2)
                tgt.Resize(, 4) = Array("", FNum, FName, PC)
           Case "AuM_Data"
                Set tgt = sh.Cells(Rows.Count, 1).End(xlUp)(2)
                tgt.Resize(, 3) = Array(FNum, FName, PC)
             Case "Retail Flash NCF by Month"
                Set tgt = sh.Cells(Rows.Count, 1).End(xlUp)(2)
                tgt.Resize(, 5) = Array("", FNum, FName, "", FNR)
            End Select
        Next
        Target.Offset(, -1).Resize(, 5).ClearContents
    End If
Exits:
    Application.EnableEvents = True
End Sub