Private Sub CommandButton1_Click()
Dim i&, Test&, ID&, Annee$
Dim F As Worksheet, TTmp As Variant, Treport As Variant
ReDim Treport(1 To 5, 1 To 1)
Treport(1, 1) = "Année": Treport(2, 1) = "ID"
Treport(3, 1) = "Pays": Treport(4, 1) = "Kg"
Treport(5, 1) = "€"
For Each F In Worksheets
If F.Name < > ActiveSheet.Name Then
Annee = F.Name
TTmp = F.Range(F.Cells(1, 1), F.Cells(Rows.Count, 1).End(3)(1, 28))
For i = LBound(TTmp, 1) To UBound(TTmp, 1)
On Error Resume Next
Test = CLng(TTmp(i, 1))
If Test < > 0 Then
ID = TTmp(i, 1)
Else
Err.Clear
If UCase(TTmp(i, 1)) < > "TOTAL" And TTmp(i, 1) < > "" Then
ReDim Preserve Treport(1 To 5, 1 To UBound(Treport, 2) + 1)
Treport(1, UBound(Treport, 2)) = Annee
Treport(2, UBound(Treport, 2)) = ID
Treport(3, UBound(Treport, 2)) = TTmp(i, 1)
Treport(4, UBound(Treport, 2)) = TTmp(i, 27)
Treport(5, UBound(Treport, 2)) = TTmp(i, 26)
End If
End If
Next i
End If
Next F
Columns("$A:$E").Clear
Cells(1, 1).Resize(UBound(Treport, 2), UBound(Treport, 1)) = Application.Transpose(Treport)
Columns("$A:$E").AutoFit
ActiveWorkbook.RefreshAll
End Sub