Private Sub CommandButton1_Click()
Dim LstRow&, LstCol$, i&, j&, K&
Dim TabSource As Variant, TabReport As Variant
Dim Discrit$
Dim FormatH As Range, FormatM As Range
Discrit = "abcd"
With Me
LstCol = .Cells(1, Columns.Count).End(xlToLeft).Column - 3
LstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
TabSource = .Range(.Cells(1, 1), .Cells(LstRow, 21))
K = Application.CountIf(.Columns("c"), "total") + 1
End With
ReDim TabReport(1 To K, 1 To LstCol)
K = 1
For i = LBound(TabSource, 2) To UBound(TabSource, 2)
TabReport(1, i) = TabSource(1, i)
Next i
For i = LBound(TabSource, 1) + 1 To UBound(TabSource, 1)
If TabSource(i, 3) = "total" Then
K = K + 1
Tmp = Split(TabSource(i, 1), "_")
Select Case Tmp(LBound(Tmp))
Case "abcd"
TabReport(K, 1) = Tmp(LBound(Tmp))
Case Else
TabReport(K, 1) = Tmp(UBound(Tmp))
End Select
For j = 2 To UBound(TabReport, 2)
TabReport(K, j) = TabSource(i, j)
Next j
End If
Next i
With Sheets.Add(after:=Sheets(Sheets.Count))
.Name = "MiseEnForme_" & Sheets.Count - 1
.Cells(1, 1).Resize(UBound(TabReport, 1), UBound(TabReport, 2)) = TabReport
.Columns.AutoFit
.Activate
End With
ActiveSheet.Range("AE2:AE" & K).FormulaR1C1 = "=CONVERT(RC[-26],""hh"",""hh"")"
ActiveSheet.Range("AO2:AO" & K).FormulaR1C1 = "=CONVERT(RC[-26],""hh"",""hh"")"
ActiveSheet.Range("AU2:AU" & K).FormulaR1C1 = "=CONVERT(RC[-26],""hh"",""hh"")"
ActiveSheet.Range("AG2:AH" & K).FormulaR1C1 = "=CONVERT(RC[-26],""hh"",""hh"")"
ActiveSheet.Range("AS2:AT" & K).FormulaR1C1 = "=CONVERT(RC[-26],""hh"",""hh"")"
ActiveSheet.Range("AE2:AE" & K).Select: Selection.Copy: ActiveSheet.Range("E2").Select: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("AO2:AO" & K).Select: Selection.Copy: ActiveSheet.Range("O2").Select: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("AU2:AU" & K).Select: Selection.Copy: ActiveSheet.Range("U2").Select: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("AG2:AH" & K).Select: Selection.Copy: ActiveSheet.Range("G2").Select: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("AS2:AT" & K).Select: Selection.Copy: ActiveSheet.Range("S2").Select: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Columns("AE:AT").Select
Selection.Delete Shift:=xlToLeft
With ActiveSheet
Set FormatH = Application.Union(ActiveSheet.Range("E2:E" & K), ActiveSheet.Range("O2:O" & K), ActiveSheet.Range("U2:U" & K))
Set FormatM = Application.Union(ActiveSheet.Range("G2:H" & K), ActiveSheet.Range("S2:T" & K))
End With
FormatH.Select
Selection.NumberFormat = "[h]:mm:ss"
FormatM.Select
Selection.NumberFormat = "[mm]:ss"
ActiveSheet.Range("A1").Select
End Sub