insertion automatique de ligne et remplissage selon recherche H sur autre feuille

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B2:B65536]) Is Nothing _
  Or Target.Count > 1 Then Exit Sub
Dim lig As Variant
With Feuil1 'CodeName
  lig = Application.Match(Target, .[B:B], 0)
  If IsNumeric(lig) And UCase(Target.Offset(-1, 3)) <> "X" Then
    Target.EntireRow.Insert
    Target.Offset(-1, 3) = "X" 'repérage
    Target.Offset(-1, -1).Resize(, 4) = .Cells(lig - 1, 1).Resize(, 4).Value
  End If
End With
End Sub

Private Sub CommandButton1_Click()
Dim Target As Range, lig As Variant
For Each Target In Range("B2", [B65536].End(xlUp))
  With Feuil1 'CodeName
    lig = Application.Match(Target, .[B:B], 0)
    If IsNumeric(lig) And UCase(Target.Offset(-1, 3)) <> "X" Then
      Target.EntireRow.Insert
      Target.Offset(-1, 3) = "X" 'repérage
      Target.Offset(-1, -1).Resize(, 4) = .Cells(lig - 1, 1).Resize(, 4).Value
    End If
  End With
Next
End Sub

Application.ScreenUpdating = False 'fige l'écran

If lig > 2 Then

Private Sub CommandButton1_Click()
Dim N As Integer, Target As Range, lig As Variant
Application.ScreenUpdating = False 'fige l'écran
With Feuil1 'CodeName
  N = .Cells(1, .Columns.Count).End(xlToLeft).Column 'nombre de colonnes
  For Each Target In Range("B2", Cells(Rows.Count, 2).End(xlUp))
    lig = Application.Match(Target, .[B:B], 0)
    If IsNumeric(lig) And UCase(Target.Offset(-1, N - 1)) <> "X" Then
      If lig > 2 Then
        Target.EntireRow.Insert
        Target.Offset(-1, N - 1) = "X" 'repérage
        Target.Offset(-1, -1).Resize(, N) = .Cells(lig - 1, 1).Resize(, N).Value
      End If
    End If
  Next
End With
End Sub

Private Sub CommandButton1_Click()
Dim N As Integer, Target As Range, lig As Variant
Application.ScreenUpdating = False 'fige l'écran
With Feuil1 'CodeName
  N = .Cells(1, .Columns.Count).End(xlToLeft).Column 'nombre de colonnes
  For Each Target In Range("B2", Cells(Rows.Count, 2).End(xlUp))
    lig = Application.Match(Target, .[B:B], 0)
    If IsNumeric(lig) Then
      If lig > 2 And UCase(.Cells(lig - 1, N)) <> "X" Then
        Target.EntireRow.Insert
        .Cells(lig - 1, N) .Resize(2) = "X" 'repérage des 2 lignes
        Target.Offset(-1, -1).Resize(, N) = .Cells(lig - 1, 1).Resize(, N).Value
      End If
    End If
  Next
End With
End Sub