Koen a un classeur contenant 150 feuilles de travail, une pour chaque branche de l’entreprise. Il doit trier ces feuilles en fonction de la région à laquelle appartient chaque branche. (Il y a cinq régions dans son entreprise.) Il note également qu’il serait bien que la couleur de l’onglet de chaque feuille de calcul reflète la région, et se demande si un tel tri est possible dans Excel.

Oui, un tel tri est possible, mais il nécessite l’utilisation d’une macro.

La partie la plus difficile de la création de la macro est de définir comment vous déterminez quelles branches se trouvent dans quelle région. Le moyen le plus simple de procéder consiste peut-être à vous assurer que vos feuilles de calcul utilisent un modèle de dénomination qui inclut à la fois les informations de région et de branche. Par exemple, vous pouvez nommer les feuilles de calcul quelque chose comme «Reg01-Branch123». Votre macro pourrait ensuite parcourir chaque feuille de calcul et effectuer à la fois la coloration et le tri.

Sub SortWorksheets()

Dim iReg As Integer     Dim I As Integer     Dim J As Integer     Dim K As Integer     Dim ws As Worksheet

Application.ScreenUpdating = False

' Set tab colors     For Each ws in Worksheets         iReg = Val(Mid(ws.Name,4,2))

Select Case iReg             Case 1                 ws.Tab.Color = vbRed             Case 2                 ws.Tab.Color = vbYellow             Case 3                 ws.Tab.Color = vbBlue             Case 4                 ws.Tab.Color = vbGreen             Case 5                 ws.Tab.Color = vbCyan             Case Else                 ws.Tab.ColorIndex = xlColorIndexNone         End Select     Next ws

' Sort the worksheets     For I = 1 To Sheets.Count - 1         K = I         For J = I + 1 To Sheets.Count             If UCase(Sheets(K).Name) > UCase(Sheets(J).Name) Then K = J         Next J         If K <> I Then Sheets(K).Move Before:=Sheets(I)

Next I

Application.ScreenUpdating = True End Sub

La macro fonctionne deux fois dans la collection Worksheets. Lors du premier passage, le numéro de région est extrait du nom de la feuille de calcul.

Ceci est ensuite utilisé (dans la structure Select Case) pour définir la couleur de l’onglet.

Si le numéro de région est en dehors de la plage de 1 à 5, aucune modification n’est apportée à la couleur de l’onglet.

Si votre détermination de la branche dans quelle région est plus complexe, vous devrez ajuster la macro en conséquence. Par exemple, vous pouvez avoir une feuille de calcul nommée « Clé de région » qui contient, dans la colonne A, les noms de chacune de vos branches et, dans la colonne B, le numéro de région correspondant pour chacune de ces branches. En supposant que les données réelles commencent à la ligne 2, vous pouvez modifier la macro de la manière suivante:

Sub SortWorksheets2()

Dim sTemp As String     Dim iReg As Integer     Dim I As Integer     Dim J As Integer     Dim K As Integer     Dim ws As Worksheet     Dim key As Worksheet

Application.ScreenUpdating = False

Set key = Worksheets("Region Key")



' Set tab colors     For Each ws in Worksheets         sTemp = UCase(ws.Name)

I = 2    ' Beginning row number         iReg = 0         While key.Cells(I, 1) > ""

If UCase(key.Cells(I, 1)) = sTemp Then iReg = key.Cells(I, 2)

I = I + 1         Wend         Select Case iReg             Case 1                 ws.Tab.Color = vbRed             Case 2                 ws.Tab.Color = vbYellow             Case 3                 ws.Tab.Color = vbBlue             Case 4                 ws.Tab.Color = vbGreen             Case 5                 ws.Tab.Color = vbCyan             Case Else                 ws.Tab.ColorIndex = xlColorIndexNone         End Select     Next ws

' Sort the worksheets     For I = 1 To Sheets.Count - 1         K = I         For J = I + 1 To Sheets.Count             If UCase(Sheets(K).Name) > UCase(Sheets(J).Name) Then K = J         Next J         If K <> I Then Sheets(K).Move Before:=Sheets(I)

Next I     Sheets("Region Key").Move Before:=Sheets(1)



Application.ScreenUpdating = True End Sub

La plus grande différence entre cette macro et la précédente est que celle-ci saisit le numéro de région d’une feuille de calcul. En tant que dernière action de la macro, la feuille de calcul «Clé de région» est déplacée au tout début de la collection Worksheets.

Notez que cette deuxième macro peut également donner à vos onglets de feuille de calcul, en bas de la fenêtre du programme, l’apparence d’un kaléidoscope de couleurs.

La raison en est que les onglets sont triés en fonction de leurs noms et non de leurs couleurs. Cela diffère de la première macro, qui est effectivement triée par région, puis par branche, car les feuilles de calcul ont été nommées à l’aide de ce modèle. Si vous souhaitez toujours utiliser la « Clé Région »

approchez et triez par région puis branchez, vous pouvez le faire en ajustant un peu plus la macro:

Sub SortWorksheets3()

Dim sTemp As String     Dim sSortArray(499) As String     Dim iReg As Integer     Dim I As Integer     Dim J As Integer     Dim K As Integer     Dim ws As Worksheet     Dim key As Worksheet

Application.ScreenUpdating = False

Set key = Worksheets("Region Key")



' Set tab colors and build sort array     J = 0     For Each ws in Worksheets         sTemp = UCase(ws.Name)

I = 2    ' Beginning row number         iReg = 0         While key.Cells(I, 1) > ""

If UCase(key.Cells(I, 1)) = sTemp Then iReg = key.Cells(I, 2)

I = I + 1         Wend

J = J + 1         sSortArray(J) = Right("00" & iReg, 2) & " " & ws.Name

Select Case iReg             Case 1                 ws.Tab.Color = vbRed             Case 2                 ws.Tab.Color = vbYellow             Case 3                 ws.Tab.Color = vbBlue             Case 4                 ws.Tab.Color = vbGreen             Case 5                 ws.Tab.Color = vbCyan             Case Else                 ws.Tab.ColorIndex = xlColorIndexNone                 ' Force into incorrect region area for sort                 sSortArray(J) = "00 " & ws.Name         End Select     Next ws

' Sort the worksheets     For I = 1 To Sheets.Count - 1         K = I         For J = I + 1 To Sheets.Count             If UCase(sSortArray(K)) > UCase(sSortArray(J)) Then K = J         Next J         If K <> I Then             Sheets(K).Move Before:=Sheets(I)

sTemp = sSortArray(K)

For J = K To I Step -1                 sSortArray(J) = sSortArray(J-1)

Next J             sSortArray(I) = sTemp         End If     Next I     Sheets("Region Key").Move Before:=Sheets(1)



Application.ScreenUpdating = True End Sub

Notez que cette itération de la macro repose sur un tableau d’assistance (sSortArray) pour suivre la manière dont les noms dans les feuilles de calcul doivent être triés.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13710) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.