Sub Test()
Dim Sh As Worksheet, ws As Worksheet, P As Range, c1 As Range, c2 As Range
Set Sh = Feuil1 'CodeName, à adapter
Set ws = Feuil2 'CodeName, à adapter
If Sh.FilterMode Then Sh.ShowAllData 'si la feuille est filtrée
If ws.FilterMode Then ws.ShowAllData 'si la feuille est filtrée
Set P = ws.Range("A4:A" & ws.Range("B" & ws.Rows.Count).End(xlUp).Row)
If P.Row < 4 Then Exit Sub
Set c1 = Sh.Cells.Find("XX", , xlValues)
Set c2 = Sh.Cells(Sh.Rows.Count, c1.Column).End(xlUp)(2)
c2.Resize(P.Count) = P.Value 'copie les valeurs
Sh.Range(c1, c2.Resize(P.Count)).RemoveDuplicates 1, xlNo 'supprime les doublons
End Sub