Private Sub Worksheet_Activate()
Dim ncol%, tablo, resu(), i&, j%, n&
With Feuil1.[A1].CurrentRegion 'CodeName de la feuille à adapter
ncol = .Columns.Count
tablo = .Resize(, ncol + 1) 'matrice, plus rapide, au moins 2 éléments
End With
ReDim resu(1 To ncol * UBound(tablo), 1 To 2)
For i = 2 To UBound(tablo)
For j = 1 To ncol
n = n + 1
resu(n, 1) = tablo(1, j)
resu(n, 2) = tablo(i, j)
Next j, i
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A1] 'à adapter
If n Then .Resize(n, 2) = resu: .Resize(n, 2).Borders.Weight = xlThin
.Offset(n).Resize(Rows.Count - n - .Row + 1, 2).Delete xlUp 'RAZ en dessous
End With
Columns("A:B").AutoFit 'ajustement largeur
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub