Private Sub CommandButton1_Click()
Dim t, nlig&, d As Object, i&, rest()
Application.ScreenUpdating = False
With Workbooks.Open(ThisWorkbook.Path & "\Paie-Hor.xlsx").Sheets("Feuil1")
t = .Range("A5:X" & .Range("D" & .Rows.Count).End(xlUp).Row + 4)
nlig = UBound(t)
.Parent.Close False
End With
'---restitution du 1er tableau---
[D:D].Copy [AA1] 'sauvegarde la colonne D (Point Paie) en colonne auxiliaire AA
Range("A3:X" & Rows.Count).ClearContents 'RAZ
[A3].Resize(nlig, 24) = t
'---liste des Point Paie du 1er tableau---
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(t)
If t(i, 4) <> "" Then d(t(i, 4)) = i 'repère la ligne
Next i
'---création du 2ème tableau (rest)---
t = Range("Y3:AA" & Range("AA" & Rows.Count).End(xlUp).Row + 2)
ReDim rest(1 To nlig, 1 To 2)
For i = 1 To UBound(t)
If d.Exists(t(i, 3)) Then rest(d(t(i, 3)), 1) = t(i, 1): rest(d(t(i, 3)), 2) = t(i, 2)
Next i
'---restitution du 2ème tableau rest---
[AA:AA].Delete
Range("Y3:Z" & Rows.Count).ClearContents 'RAZ
[Y3].Resize(nlig, 2) = rest
End Sub