Private Sub Worksheet_Activate()
Dim ncol%, d As Object, dd As Object, tablo, i&, x$, j%, resu(), n&
ncol = 8 'nombre de colonnes restituées
Set d = CreateObject("Scripting.Dictionary")
Set dd = CreateObject("Scripting.Dictionary")
tablo = [A1].CurrentRegion.Resize(, 2) 'matrice, plus rapide
For i = 2 To UBound(tablo)
d(CStr(tablo(i, 1))) = tablo(i, 2) 'mémorise la colonne B
Next i
For j = 3 To ncol
tablo = Sheets(CStr(Cells(1, j))).Cells(1).CurrentRegion.Resize(, 2) 'matrice, plus rapide
For i = 2 To UBound(tablo)
x = CStr(tablo(i, 1))
If Not dd.exists(x) Then
n = n + 1
dd(x) = n 'mémorise la ligne
ReDim Preserve resu(1 To ncol, 1 To n)
resu(1, n) = x
resu(2, n) = d(x) 'récupère la colonne B mémorisée
End If
resu(j, dd(x)) = tablo(i, 2) 'récupère la ligne
Next i, j
'---restitution---
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2]
If n Then
.Resize(n, ncol) = Application.Transpose(resu) 'Transpose est limitée à 65536 lignes
.Resize(n, ncol).Sort .Cells, xlAscending, Header:=xlNo 'tri
End If
.Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).ClearContents 'RAZ en dessous
.Resize(, ncol).EntireColumn.AutoFit 'ajustement largeurs
End With
End Sub