my code is a problem with message box

Hi Speed

Please try the attached file- it should work for you. The modified code is below and the changes I made are in bold.

Your message box would always appear since only one sheet has the target value (so another your trigger the "Not found" and exit the sub. Instead I've acted on the absence of an error (Not IsError(res)) - in this case the flag Fnd is set to true abd the OUTPUT sheet is populated.

I also added a test so you didn't search unnecessarily for values in OUTPUT (though the vlaues would be found!) and pushed the "Not found" message to the end (if Fnd = False)

REVISION 1: Moved the line Range("E3:E7").ClearContents to after the DIM statements- your code had it happening only if a part was found and you hadTarget. in front of it (an error). Revised file attached.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim ws As Worksheet
Dim lr As Long, res As Variant, Fnd As Boolean
Range("E3:E7").ClearContents
For Each ws In Worksheets
If Not ws.Name = Target.Parent.Name Then ' don't look for values on the target sheet
    lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
    res = Application.Match(Target.Value, ws.Range("B1:B" & lr), 0)
    If Not IsError(res) Then 'only do the following if E1 contents are on this worksheet
        Fnd = True 'set a flag to say it was found
        With Target
         .Offset(2, 0).Value = ws.Cells(res, 2).Value
         .Offset(3, 0).Value = ws.Cells(res, 3).Value
         .Offset(4, 0).Value = ws.Cells(res, 4).Value
         .Offset(5, 0).Value = ws.Cells(res, 5).Value
         .Offset(6, 0).Value = ws.Cells(res, 6).Value
        End With
    End If
End If
Next ws
If Fnd = False Then MsgBox Target.Value & "- Code not found"
End Sub

Hope this fixes your problem.