Sub Macro1()
'
' Macro1 Macro
'
Range("B6:F41").Select
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add Key:=Range("B6"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Feuil1").Sort
.SetRange Range("B7:F41")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("H6:L41").Select
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add Key:=Range("H6"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Feuil1").Sort
.SetRange Range("H7:L41")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N6:R41").Select
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add Key:=Range("N6"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Feuil1").Sort
.SetRange Range("N7:R41")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub Tri()
Dim tablo1 As Range, tablo2 As Range, tablo3 As Range, ad2$, ad3$
Set tablo1 = [B6].CurrentRegion 'à adapter
Set tablo2 = [H6].CurrentRegion 'à adapter
Set tablo3 = [N6].CurrentRegion 'à adapter
ad2 = tablo2.Address: ad3 = tablo3.Address 'mémorisation
Application.ScreenUpdating = False
tablo2.Cut tablo1(tablo1.Rows.Count + 1, 1)
tablo3.Cut tablo2(tablo2.Rows.Count + 1, 1)
Range(tablo1, tablo3).Sort tablo1(1, 2), xlAscending, Header:=xlNo 'tri croissant sur les noms
tablo2.Cut Range(ad2): tablo3.Cut Range(ad3)
End Sub
Sub Tri()
Dim tablo1 As Range, tablo2 As Range, tablo3 As Range, ad2$, ad3$
Set tablo1 = [B6].CurrentRegion 'à adapter
Set tablo2 = [H6].CurrentRegion 'à adapter
Set tablo3 = [N6].CurrentRegion 'à adapter
ad2 = tablo2.Address: ad3 = tablo3.Address 'mémorisation
Application.ScreenUpdating = False
tablo2.Cut tablo1(tablo1.Rows.Count + 1, 1)
tablo3.Cut tablo2(tablo2.Rows.Count + 1, 1)
With Range(tablo1, tablo3)
.Sort .Columns(2), xlAscending, Header:=xlNo 'tri croissant sur les noms
.Columns(1) = "=IF(LEFT(RC[1])=LEFT(R[-1]C[1]),"""",UPPER(LEFT(RC[1])))" 'lettre en 1ère colonne
.Columns(1) = .Columns(1).Value
End With
tablo2.Cut Range(ad2): tablo3.Cut Range(ad3)
End Sub
Set tablo1 = [B6:F41] 'à adapter
Set tablo2 = [H6:L41] 'à adapter
Set tablo3 = [N6:R41] 'à adapter
Sub Tri()
Dim nom As Name, n%, liste$(), tablo As Range, P As Range
'---liste des noms des tableaux et de leurs adresses---
For Each nom In ThisWorkbook.Names
If nom.Name Like "Tableau#*" Then
ReDim Preserve liste(1, n) 'base 0
liste(0, n) = nom.Name
liste(1, n) = Range(nom.Name).Address
n = n + 1
End If
Next
'---groupement des tableaux les uns en dessous des autres---
Application.ScreenUpdating = False
Set tablo = Range(liste(0, 0))
Set P = tablo
For n = 1 To UBound(liste, 2)
Range(liste(0, n)).Cut tablo(tablo.Rows.Count + 1, 1) 'couper-coller
Set tablo = Range(liste(0, n))
Set P = Range(P, tablo)
Next
'---tri et lettre en 1ère colonne---
P.Sort P(1, 2), xlAscending, Header:=xlNo 'tri croissant sur les noms
P.Columns(1) = "=IF(LEFT(RC[1])=LEFT(R[-1]C[1]),"""",UPPER(LEFT(RC[1])))"
P.Columns(1) = P.Columns(1).Value 'supprime les formules
'---remise en place des tableaux---
For n = 1 To UBound(liste, 2)
Range(liste(0, n)).Cut Range(liste(1, n)) 'couper-coller
Next
With P.Parent.UsedRange: End With 'actualise la barre de défilement verticale
End Sub