write permanently to another sheet from a cell with formulated lookup value

[ajish002]

Hi

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address(0, 0) <> "J1" Then Exit Sub
    
    Dim LookupValueRow  As Variant
    
    Const DestSheet     As String = "Sheet2"    '<<<<< adjust to suit
    Const ColA_StartRow As Long = 5             '<<<<< adjust to suit
    
    If Len(Target) Then
        LookupValueRow = WorksheetFunction.Match(Target, Range("a:a"), 0)
        If Not IsError(LookupValueRow) Then
            Worksheets(DestSheet).Cells(LookupValueRow - ColA_StartRow + 1, "BL") = Range("D32").Value
        End If
    End If
        
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) <> "J1" Then Exit Sub
  Dim LookupValueRow  As Variant
  If Len(Target) Then
    LookupValueRow = WorksheetFunction.Match(Target, Worksheets("PMast").Range("A:A"), 0)
    If Not IsError(LookupValueRow) Then
      Worksheets("PMast").Cells(LookupValueRow, "BL") = Range("D32").Value
    End If
  End If
End Sub

hi this code is working perfect
the code was modified and given to me by sir Rick
thanks to u Admin for taking effort n time to help me
i know i could not convey what i needed properly due to language problem