Ajouter info à partir 2nd onglet

Option Explicit   

Sub Repartir_w()   
Dim Derlig As Byte, Cptr As Byte, Cptr_d As Byte, num As Byte   
Dim T_staff, T_hijk, T_libre   
Dim D_staff As Object   
Dim Agent As String   
   
'initialisations   
Randomize   
Application.ScreenUpdating = False   
Set D_staff = CreateObject("scripting.dictionary")   

With Sheets("list agent")   
     'mémorise la liste des agents   
     Derlig = .Columns("A").Find("*", , , , , xlPrevious).Row   
     T_staff = Application.Transpose(.Range("A2:A" & Derlig).Value)   
End With   

With Sheets("database")   
     'mémorise les critères d'attribution   
     Derlig = .Columns("A").Find("*", , , , , xlPrevious).Row   
     T_hijk = .Range("H2:K" & Derlig).Value   
End With   

'mémorise l'affectation  aléatoire d'un agent   
For Cptr = 1 To UBound(T_hijk)   
     If D_staff.Count = 0 Then ' initialisation au départ ou si il n'y a plus personne de disponible chez les agents _   
                                                            (nbre des lignes répondant aux critères > nbre agents)   
          For Cptr_d = 1 To UBound(T_staff)   
               If Not D_staff.exists(T_staff(Cptr_d)) Then D_staff.Add T_staff(Cptr_d), ""   
               T_libre = D_staff.keys   
          Next   
     End If   
        
     If T_hijk(Cptr, 1) = "05K" And _   
               (T_hijk(Cptr, 2) = "DLV" Or T_hijk(Cptr, 2) = "CLOSED" Or T_hijk(Cptr, 2) = "RCV") Then   
               'tirage au sort d'un agent   
               num = Int(Rnd * D_staff.Count)   
               Agent = T_libre(num)   
               'affectation de l'agent   
               T_hijk(Cptr, 4) = Agent   
               'suppression de l'agent de T_libre   
               D_staff.Remove (Agent)   
               T_libre = D_staff.keys   
     End If   
Next   

'restitue l'affectation des agents   
Sheets("database").Range("H2:K" & Derlig) = T_hijk   

End Sub