MacroPaf()
Dim DerL As Long, i As Long, Tabini, TabFin(), TabTemp, Deb As Byte
Dim MaChaine As String, j As Integer, x As Long
Dim FS As Worksheet, FC As Worksheet
Set FS = Worksheets("Source") ' à adapter
Set FC = Worksheets("Cible") ' à adapter
DerL = FS.Range("A" & Rows.Count).End(xlUp).Row
'** passage des dates au format standard pour éviter les pb format date US
FS.Range("I2:K" & DerL).NumberFormat = "General"
Tabini = FS.Range("A2:N" & DerL)
For i = LBound(Tabini) To UBound(Tabini)
Deb = InStr(Tabini(i, 2), "sur")
MaChaine = Replace(Right(Tabini(i, 2), Len(Tabini(i, 2)) - Deb - 3), " ", "")
TabTemp = Split(MaChaine, ",")
For j = LBound(TabTemp) To UBound(TabTemp)
x = x + 1
ReDim Preserve TabFin(1 To UBound(Tabini, 2), 1 To x)
For k = LBound(Tabini, 2) To UBound(Tabini, 2) - 1
TabFin(k, x) = Tabini(i, k)
Next
TabFin(k, x) = TabTemp(j)
Next
Next
FS.Range("I2:K" & DerL).NumberFormat = "m/d/yyyy h:mm"
FS.Range("A1:N1").Copy FC.Range("A1")
FC.Range("A2").Resize(UBound(TabFin, 2), UBound(TabFin, 1)) = Application.Transpose(TabFin)
FC.Columns("I:K").NumberFormat = "m/d/yyyy h:mm"
End Sub