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

again it writes in sheet2 column BL 2nd row(BL2) when lookupvalue (ie J1) is given "2"
but since "2"(J1) is in columnA row3 in sheet2 output should be in BL3