XL 2019 Eviter les doublons

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim adr$, k As Byte, ref$, cel As Range
  With Target
    If .CountLarge > 1 Then Exit Sub
    adr = .Address(0, 0)
    If adr = "A3" Then k = 1
    If adr = "C3" Then k = 2
    If k = 0 Then Exit Sub
    ref = .Value: If ref = "" Then Exit Sub
    Set cel = Worksheets("Données").Columns(k).Find(ref, , -4163, 1, 1)
    If cel Is Nothing Then Exit Sub
    MsgBox "le n° " & ref & " est déjà saisi.", 48, "pas accepté :"
    Application.EnableEvents = 0: .Value = ""
    Application.EnableEvents = -1
  End With
End Sub

Option Explicit

Sub ajout_Facture()
  If ActiveSheet.Name <> "Saisie" Then Exit Sub
  Dim n&: Application.ScreenUpdating = 0
  With Worksheets("Données")
    With .ListObjects("Tableau1")
      If .DataBodyRange Is Nothing Then n = 0 Else n = .ListRows.Count
    End With
    With .Cells(n + 4, 1)
      .Value = [A3]        'N° BL
      .Offset(, 1) = [C3]  'N° Facture
      .Offset(, 2) = [E3]  'Date
      .Offset(, 3) = [G3]  'Montant
    End With
  End With
  [A3, C3, E3, G3].ClearContents
End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim adr$, k As Byte, ref$, cel As Range
  With Target
    If .CountLarge > 1 Then Exit Sub
    adr = .Address(0, 0)
    If adr = "A6" Then k = 1
    If adr = "C6" Then k = 2
    If k = 0 Then Exit Sub
    ref = .Value: If [B2] = "" Or ref = "" Then Exit Sub
    Set cel = Worksheets([B2]).Columns(k).Find(ref, , -4163, 1, 1)
    If cel Is Nothing Then Exit Sub
    MsgBox "le n° " & ref & " est déjà saisi.", 48, "pas accepté :"
    Application.EnableEvents = 0: .Value = ""
    Application.EnableEvents = -1
  End With
End Sub

Option Explicit

Sub ajout_Facture()
  If ActiveSheet.Name <> "Saisie" Then Exit Sub
  If [B2] = "" Then Exit Sub
  Dim n&: Application.ScreenUpdating = 0
  With Worksheets([B2])
    With .ListObjects(1)
      If .DataBodyRange Is Nothing Then n = 0 Else n = .ListRows.Count
    End With
    With .Cells(n + 4, 1)
      .Value = [A6]        'N° BL
      .Offset(, 1) = [C6]  'N° Facture
      .Offset(, 2) = [E6]  'Date
      .Offset(, 3) = [G6]  'Montant
    End With
  End With
  [A6, C6, E6, G6].ClearContents
End Sub