Private Sub Worksheet_Activate()
Dim resu(), tablo, i&, n&
ReDim resu(1 To Rows.Count, 1 To 8)
'---feuille encours---
tablo = Feuil1.UsedRange.Resize(, 12)
For i = 2 To UBound(tablo)
If tablo(i, 4) <> "" Then
n = n + 1
resu(n, 1) = tablo(i, 4)
resu(n, 2) = tablo(i, 1) 'correspond au Tiers Payeur
resu(n, 3) = tablo(i, 3)
resu(n, 6) = tablo(i, 9)
resu(n, 7) = tablo(i, 12)
resu(n, 8) = Feuil1.Name 'repérage feuille
End If
Next
'---feuille tva---
tablo = Feuil2.UsedRange.Resize(, 34)
For i = 2 To UBound(tablo)
If tablo(i, 1) <> "" Then
n = n + 1
resu(n, 1) = tablo(i, 1)
resu(n, 2) = tablo(i, 7)
resu(n, 3) = tablo(i, 19)
resu(n, 4) = tablo(i, 15)
resu(n, 5) = tablo(i, 17)
resu(n, 6) = tablo(i, 16)
resu(n, 7) = tablo(i, 34)
resu(n, 8) = Feuil2.Name 'repérage feuille
End If
Next
'---feuille notva---
tablo = Feuil3.UsedRange.Resize(, 28)
For i = 2 To UBound(tablo)
If tablo(i, 23) <> "" Then
n = n + 1
resu(n, 1) = tablo(i, 23)
resu(n, 2) = tablo(i, 12)
resu(n, 3) = tablo(i, 26)
resu(n, 6) = tablo(i, 28)
resu(n, 7) = tablo(i, 27)
resu(n, 8) = Feuil3.Name 'repérage feuille
End If
Next
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de destination, à adapter
If n Then .Resize(n, 8) = resu
.Offset(n).Resize(Rows.Count - n - .Row + 1, 8).ClearContents 'RAZ en dessous
End With
Columns.AutoFit 'ajustement largeurs
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub