Public Sub compil()
Const cellBase = "b14" 'cellCible est l'adresse de la cellule
'de départ des résultats sur la feuille "recap"
Dim iRecap&, i&, nlig&, tablo, Base As Range, cible As Range, n&
Application.ScreenUpdating = False
iRecap = Worksheets("recap").Index
Set Base = Worksheets("recap").Range(cellBase)
Set cible = Worksheets("recap").Range(cellBase)
For i = 1 To iRecap - 1
With Worksheets(i)
nlig = .Cells(Rows.Count, "a").End(xlUp).Row
If nlig > 1 Then
.Range("a2:a" & nlig).Copy cible
cible.Offset(, 1).Resize(nlig - 1) = .Name
cible.Offset(, 2).Resize(nlig - 1).Formula = "=ROW()+ 1 - " & cible.Row
cible.Offset(, 2).Resize(nlig - 1) = cible.Offset(, 2).Resize(nlig - 1).Value
cible.Offset(, 3).Resize(nlig - 1).Formula = "=ROW()+ 1 - " & Base.Row
cible.Offset(, 3).Resize(nlig - 1) = cible.Offset(, 3).Resize(nlig - 1).Value
Set cible = cible.Offset(nlig - 1)
End If
End With
Next i
cible.Resize(Rows.Count - cible.Row + 1, 4).ClearContents
End Sub
Private Sub Worksheet_Activate()
compil
End Sub