Sub recap()
Dim Ws As Worksheet, DerLi As Long, DerCol As Long
With Sheets("GLOBAL") 'il faut une ligne d'entêtes dans global
DerCol = Sheets("siemens").Cells.Find("*", [A1], , , 2, 2).Column
Sheets("siemens").Range("A8", Sheets("siemens").Cells(8, DerCol)).Copy Destination:=.Range("A8")'entête
DerLi = .Cells.Find("*", [A1], , , 1, 2).Row
DerCol = .Cells.Find("*", [A1], , , 2, 2).Column
If DerLi > 8 Then .[A9].Resize(DerLi, DerCol).ClearContents
End With
For Each Ws In Worksheets
Select Case Ws.Name
Case "siemens", "cn", "abb", "telemecanique", "schneider", "kuka"
DerLi = Ws.Cells.Find("*", [A1], , , 1, 2).Row
DerCol = Ws.Cells.Find("*", [A1], , , 2, 2).Column
'avec formules
' Ws.[A9].Resize(Ws.[A65536].End(xlUp).Row - 1, 40).Copy Destination:=Worksheets("Global").[A65536].End(xlUp).Offset(1, 0)
'sans formules
Ws.[A9].Resize(DerLi, DerCol).Copy
Worksheets("Global").[A65536].End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Select
Next Ws
End Sub