Sub TriFeuilles()
Application.EnableEvents = False
Dim tabFeuille() As Variant 'déclare un tableau
nb = Sheets.Count - 2
ReDim tabFeuille(1 To nb, 2) 'dimensionne le tableau de "nombres de feuilles -1" lignes et 2 colonnes
i = 1
For Each ws In Sheets 'pour chaque feuille du classeurs
If ws.Name <> "NOUVEAU MODELE" And ws.Name <> "Feuil1" Then
tabFeuille(i, 1) = DateSerial(Right(Split(ws.Name, " ")(0), 2), Mid(Split(ws.Name, " ")(0), 3, 2), Left(Split(ws.Name, " ")(0), 2)) 'première colonne prend la date
' MsgBox Left(Split(ws.Name, " ")(0), 2)
' MsgBox Mid(Split(ws.Name, " ")(0), 3, 2)
' MsgBox Right(Split(ws.Name, " ")(0), 2)
' MsgBox DateSerial(Right(Split(ws.Name, " ")(0), 2), Mid(Split(ws.Name, " ")(0), 3, 2), Left(Split(ws.Name, " ")(0), 2))
tabFeuille(i, 2) = Split(ws.Name, " ")(1) 'seconde colonne prend la ville
i = i + 1
End If
Next ws
Sheets("Feuil1").Range("A1").Resize(UBound(tabFeuille, 1), 3) = tabFeuille
With Sheets("Feuil1")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("B1:B" & nb), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("C1:C" & nb), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Feuil1").Sort
.SetRange Range("B1:C" & nb)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
tabFeuille = .Range("B1:C" & nb).Value
End With
LastFeuille = "NOUVEAU MODELE"
For i = LBound(tabFeuille, 1) To UBound(tabFeuille, 1) 'on parcourt le tableau
Sheets("" & Format(tabFeuille(i, 1), "ddmmyy") & " " & tabFeuille(i, 2) & "").Move after:=Sheets(LastFeuille) 'et on déplace la feuille
LastFeuille = Format(tabFeuille(i, 1), "ddmmyy") & " " & tabFeuille(i, 2)
Next i
Application.EnableEvents = True
End Sub