Rechercher 1 Valeur sur plusieurs feuilles

=RECHERCHEV("remise";INDIRECT(B2 & "!$A:$B");2)

Option Explicit
Dim flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim data1 As String
Dim cellule As Range
If flag = True Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim dl1 As Long ' dernière ligne
With Sheets(Target.Worksheet.Name)
dl1 = .Range("b65536").End(xlUp).Row
On Error GoTo suite
If Not Intersect(Target, Range("b2:b" & dl1)) Is Nothing Then
    flag = True
   Target.Offset(0, 1).Value = Sheets(CStr(Target.Value)).Range("b15").Value

End If


End With
flag = False
Exit Sub
suite:
Call MsgBox("Le code fournisseur n'existe pas" _
            & vbCrLf & "" _
            , vbCritical, "Problème")
flag = False
End Sub