Private Sub Test()
    Dim S, FNum, FName, PC, sh As Worksheet, tgt As Range
    On Error GoTo Exits
    Application.EnableEvents = False
    Set Target = Sheets("CALC Sheet").Cells(2, 2)
    If Target.Address = "$B$2" Then
        S = Target.Offset(, -1)
        PC = Target.Offset(, 3)
        FName = Target.Offset(, 1)
        FNR = Target.Offset(, 2)
        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