Sub RecupDefautsII()
Dim source As Worksheet
Dim pf As Range
Set source = Sheets("Original")
source.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "TEST"
Sheets("TEST").Activate
' Suppression des colonnes qui ne servent pas
Application.ScreenUpdating = False
Columns("A:I").Delete: Columns("B:D").Delete: Columns("C:D").Delete
Columns("D:J").Delete: Columns("E:G").Delete: Columns("F:V").Delete
Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo:=Array(Array(1, 9), Array(2, 1)), _
TrailingMinusNumbers:=True
Range("A1").CurrentRegion.AutoFilter Field:=5, Criteria1:="<>*.*", Operator:=xlAnd
Set pf = [_FilterDatabase]
pf.Offset(1).Resize(pf.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
ActiveSheet.AutoFilterMode = False: Range("E1") = Empty
'
'Supprimer la colonne B
Columns("B:B").Delete
'
''Remplacer le texte".." par du vide
Range("A1:C" & Range("A65536").End(xlUp).Row).Replace What:="Time:", Replacement:=""
' transformer date en jour pour colonne A
For Each c In Range("A1:A" & Range("A" & Application.Rows.Count).End(xlUp).Row)
c.Value = Left(c, 10)
c.NumberFormat = "dddd"
Next c
'
' transformer date en date pour colonne B
For Each d In Range("B1:B" & Range("B" & Application.Rows.Count).End(xlUp).Row)
d.Value = Left(d, 10)
d.NumberFormat = "dd/mm/yyyy"
Next d
' remplacer les points par deux points pour heure
Columns("C:C").Replace What:=".", Replacement:=":"
'
'transformer date en date pour colonne C
For Each e In Range("C1:C" & Range("C" & Application.Rows.Count).End(xlUp).Row)
e.Value = Right(e, 8)
e.NumberFormat = "hh:mm:ss"
Next e
'Mise en forme
Columns("A:A").ColumnWidth = 8: Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 9: Columns("D:D").ColumnWidth = 25
Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets("TEST").Range("A1").EntireRow.Insert
Sheets("TEST").Range("A1") = "ENT1"
Sheets("TEST").Range("A1").AutoFill Destination:=Sheets("TEST").Range("A1").Range("A1:D1"), Type:=xlFillDefault
doublons
Application.ScreenUpdating = True
End Sub