Private Sub Worksheet_Activate()
Dim source As Range, titre$, dest As Range, td, nlig&, ncol%, i&, j%, memi&, memj%
Set source = Feuil2.UsedRange 'CodeName de la feuille
titre = source(1) 'Nom PLV
Set dest = Me.UsedRange.Resize(, 9) 'largeur 9 colonnes à adapter
td = dest 'matrice, plus rapide
nlig = UBound(td)
ncol = UBound(td, 2) - 3
'---RAZ du tableau de destination---
For i = 1 To nlig
For j = 1 To ncol
If td(i, j) Like titre & "*" Then
td(i, j + 1) = ""
td(i + 1, j + 1) = ""
td(i + 1, j + 3) = ""
End If
Next j, i
'---remplissage du tableau de destination---
memi = 1: memj = 1
For Each source In source.Offset(1).Columns(1).Cells
If source <> "" Then
For i = memi To nlig
For j = memj To ncol
If td(i, j) Like titre & "*" Then
td(i, j + 1) = source
td(i + 1, j + 1) = source(1, 2)
td(i + 1, j + 3) = source(1, 3)
If j = ncol Then memi = i + 2
memj = IIf(j = ncol, 1, j + 4)
GoTo 1
End If
If j = ncol Then memi = i + 1: memj = 1
Next j, i
End If
1 Next
dest = td
End Sub