Option Explicit
Private Sub Worksheet_Activate()
Rapport Year(Date)
End Sub
Sub Rapport(Optional ByVal An)
Dim T(), Dic As Dictionary, Données As Collection, Tit() As String, Site As SsGroup, _
Nom As SsGroup, Prénom As SsGroup, Agent As SsGroup, L&, K(), N&, Détail
T = PlgUti(Feuil1.Rows(4)).Value
Set Dic = DicInvent(T, 7, ColDép:=5)
If Not IsMissing(An) Then
Dim TLgn() As Long: ReDim TLgn(1 To UBound(T))
For L = 1 To UBound(T)
If Year(T(L, 9)) = An Then N = N + 1: TLgn(N) = L
Next L
ReDim Preserve TLgn(1 To N): Préfiltrer TLgn: L = 0: End If
Set Données = GroupOrg(T, 2, 3, 4, 5, , 9)
For Each Site In Données
For Each Nom In Site.Contenu
For Each Prénom In Nom.Contenu
L = L + Prénom.Count
Next Prénom, Nom, Site
ReDim Tit(5 To 4 + Dic.Count)
For Each Nom In GroupOrg(T, 7): Tit(Dic(Nom.Id)) = Nom.Contenu(1)(6) & " — " & Nom.Id: Next Nom
ReDim T(1 To 1 + L, 1 To 4 + Dic.Count)
T(1, 1) = "SITE": T(1, 2) = "NOM": T(1, 3) = "PRÉNOM": T(1, 4) = "AGENT"
For N = 5 To UBound(Tit): T(1, N) = Tit(N): Next N
L = 1
For Each Site In Données
For Each Nom In Site.Contenu
For Each Prénom In Nom.Contenu
For Each Agent In Prénom.Contenu
L = L + 1: T(L, 1) = Site.Id: T(L, 2) = Nom.Id: T(L, 3) = Prénom.Id: T(L, 4) = Agent.Id
For Each Détail In Agent.Contenu
T(L, Dic(Détail(7))) = Détail(9)
Next Détail, Agent, Prénom, Nom, Site
ValPlgAju(PlgUti(Me.[A3])) = T
End Sub