Sub test()
Dim Sh As Worksheet, Rg As Range, Rg1 As Range
Dim DerLig As Long, DerCol As Integer
Dim PremLig As Long, PremCol As Integer, X As Long
Application.ScreenUpdating = False
'Efface le contenu de la feuille recap
Worksheets("Recap").Cells.Clear
For Each Sh In ThisWorkbook.Worksheets
If UCase(Sh.Name) <> "recap" Then
With Sh
If Not IsEmpty(.UsedRange) Then
Depart = .Cells(.Rows.Count, .Columns.Count).Address
'Trouve la première ligne occupée
PremLig = .Cells.Find(What:="*", _
After:=.Range(Depart), _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Row
'Trouve la dernière occupée dans la feuille
DerLig = .Cells.Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Trouve la dernière colonne de la feuille
DerCol = .Cells.Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
'Trouve la première colonne de la feuille
PremCol = .Cells.Find(What:="*", _
After:=.Range(Depart), _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext).Column
'Rg est une variable de type "Range" qui représente
'la plage à copier dans la feuille recap.
Set Rg = .Range(.Cells(PremLig, PremCol), .Cells(DerLig, DerCol))
End If
End With
With Worksheets("recap")
X = .Cells(.Rows.Count, 1).End(xlUp).Row
If X <> 1 Then
X = X + 1
Set Rg = Rg.Offset(1).Resize(Rg.Rows.Count - 1)
End If
Rg.Copy .Range("A" & X)
End With
End If
Next
Application.ScreenUpdating = True
End Sub