Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment trier les feuilles de calcul par ordre alphabétique / alphanumérique dans Excel?

Normalement, vous pouvez trier ou organiser l'ordre des onglets de feuille de calcul dans Excel en faisant glisser et en déposant les onglets de feuille sur la barre d'onglets de feuille. Mais pour ce faire avec plusieurs feuilles de calcul, vous pouvez envisager les méthodes délicates suivantes pour trier rapidement les feuilles de calcul par ordre alphabétique / alphanumérique dans un grand classeur.

Trier les feuilles de calcul par ordre alphabétique / alphanumérique avec le code VBA
Trier les feuilles de calcul par ordre alphabétique / alphanumérique avec Kutools for Excel


Trier les feuilles de calcul par ordre alphabétique / alphanumérique avec le code VBA

Il existe une macro pour trier les feuilles de calcul par alpha publiée dans le centre de support Microsoft. Nous pouvons l'appliquer avec les étapes suivantes:

1.  Maintenez la touche ALT + F11 clés, et il ouvre le Microsoft Visual Basic pour applications fenêtre.

2.  Cliquez insérer > Module, puis collez la macro suivante dans le Fenêtre du module.

VBA: trier les feuilles par ordre alphabétique / alphanumérique

Sub SortWorkBook()
'Updateby20140624
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3. appuie sur le F5 clé pour exécuter cette macro. Dans la boîte de dialogue suivante, cliquez sur Oui, toutes les feuilles de calcul seront triées par ordre alphabétique croissant; et cliquez Non, toutes les feuilles de calcul seront triées par ordre alphabétique décroissant.


Trier les feuilles de calcul par ordre alphabétique / alphanumérique avec Kutools for Excel

Si vous n'êtes pas familiarisé avec les macros ou préférez d'autres méthodes, vous pouvez essayer Kutools pour Excel. Kutools pour Excel's Trier les feuilles L'outil peut trier toutes les feuilles de calcul facilement.

Avant d'appliquer Kutools pour Excel, S'il vous plaît téléchargez et installez-le d'abord.

1. Cliquez Kutools Plus > Feuille > Trier les feuilles. Voir la capture d'écran:

2. Dans le Trier les feuilles boîte de dialogue, sélectionnez un type de tri dont vous avez besoin dans le volet droit, tel que Tri alpha, Tri alphanumérique, puis cliquez sur le OK bouton. Voir la capture d'écran:

Ensuite, toutes les feuilles de calcul sont triées en fonction du type de tri spécifié. Voir la capture d'écran:

doc-feuilles-de-tri6

Kutools pour Excel's Trier les feuilles L'outil peut réorganiser rapidement toutes les feuilles de calcul dans le classeur actif. Il prend en charge plusieurs types de tri, notamment Tri alpha, Tri alphanumérique, Tri des couleurs à ces émotions et de Inverser. En outre, vous pouvez également déplacer les feuilles de calcul vers le haut / bas et réinitialiser le tri.

  Si vous souhaitez bénéficier d'un essai gratuit (30 jours) de cet utilitaire, veuillez cliquer pour le télécharger, puis passez à appliquer l'opération selon les étapes ci-dessus.


Démo: triez toutes les feuilles de calcul par ordre alphabétique / alphanumérique


Article connexe:

Trier les onglets de la feuille de calcul par couleur


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    shawn · 1 months ago
    Does anyone know how I would negate text from this macro? for example if my sheets were named "cafe 1st floor" and "kitchen 2nd floor" but id like to get rid of "cafe" and "kitchen" 

    thank you!
  • To post as a guest, your comment is unpublished.
    Imd · 1 years ago
    not working if your sheet was number ex: 1, 2, 10 12,
    after sort: 1, 10, 12, 2

    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Imd,
      Do you mean all your sheet names are numbers and want to sort them ascending or descending? You can try the below VBA.

      Sub Test1()
      Dim i As Integer, j As Integer
      For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1
      If Val(Replace(UCase(Sheets(j).Name), "SHEET", "")) > Val(Replace(UCase(Sheets(j + 1).Name), "SHEET", "")) Then Sheets(j).Move After:=Sheets(j + 1)
      Next j
      Next i
      End Sub
  • To post as a guest, your comment is unpublished.
    Kathy · 1 years ago
    Thank you for the macro !!!
  • To post as a guest, your comment is unpublished.
    Mahir · 2 years ago
    Thank you for your help, very much appreciated...
  • To post as a guest, your comment is unpublished.
    Sarah · 3 years ago
    I don't know VBA at all but your instructions worked perfectly. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Mahir · 3 years ago
    Thank you :)
  • To post as a guest, your comment is unpublished.
    SAM · 3 years ago
    Very helpful :) Thank you!
  • To post as a guest, your comment is unpublished.
    Sagar · 3 years ago
    Thank you sooooooo much !!!
  • To post as a guest, your comment is unpublished.
    Varun Shah · 4 years ago
    Thanks a Lot!!!
  • To post as a guest, your comment is unpublished.
    Chris Hall · 4 years ago
    I so appreciate this ... thank you...thank you...Thank You!~chrissy
  • To post as a guest, your comment is unpublished.
    Roxanne · 4 years ago
    I love you right now. Thank you!
  • To post as a guest, your comment is unpublished.
    mozay · 4 years ago
    thanks so much guys this saved me alot of time
  • To post as a guest, your comment is unpublished.
    eka · 4 years ago
    Thanks so much..
  • To post as a guest, your comment is unpublished.
    liezl · 4 years ago
    yes! you help me a lot. big thanks :-)
  • To post as a guest, your comment is unpublished.
    Jae · 4 years ago
    Woohoo! Thanks so very much!!!!! :lol:
  • To post as a guest, your comment is unpublished.
    Terri A. Lapwing · 4 years ago
    Good morning, On an Excel spread sheet, you can group columns of data. They condense and are represented by a + sign; if you want to expand them, a line appears along with the - sign. Can you perform a similar task with worksheets inside of an Excel workbook. I would like to condense a set of worksheets of a particular fiscal quarter.
    Please help,
    Terri
  • To post as a guest, your comment is unpublished.
    Peggy · 4 years ago
    excellent!!! this saved me a lot of time!
  • To post as a guest, your comment is unpublished.
    racsar · 4 years ago
    [b]Omg, such a time saver! [/b]
    I have a list to keep track of which books I've read by favorite serial-writer authors. A different worksheet for each author got plugged-in very haphazardly as I discovered new authors differentiating them only by tab colors. Over the years it has grown to [b]37 worksheets[/b]. For some reason it just occurred to me today that it would be darn nice to have them in alphabetical order. A task which seemed daunting when I thought of pushing them around by hand.
    I have an old 2003 vs of Excel, running in Windows 10. Also, I'm no Tech, I'm completely autodidactic, so attempting anything of this sort (for me) is always risky. Amazingly, it worked like a charm.
    THANK YOU, so much!

    I have bookmarked you, I WILL be back.
  • To post as a guest, your comment is unpublished.
    josphat · 5 years ago
    This is amazing. Perfect. Thank you
  • To post as a guest, your comment is unpublished.
    Aziz · 5 years ago
    Thank you a lot many many thanks
  • To post as a guest, your comment is unpublished.
    John Machin · 5 years ago
    Macro worked first time, thank -you
  • To post as a guest, your comment is unpublished.
    Ming · 5 years ago
    Thanks for the vba codes, it works1

    One suggestion:
    After the dim comment add the following:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Before the end sub comment, add the following
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    This will speed up the process.
  • To post as a guest, your comment is unpublished.
    Wasim Aftab · 5 years ago
    Thanks,
    It works Great.
  • To post as a guest, your comment is unpublished.
    Rosie · 5 years ago
    This macro worked great for me for a while! I have been using it in a huge spreadsheet at work, and it has been great for keeping it organized as I am continually adding new tabs. However, today I got this error message:

    "Excel was able to open the file by repairing or removing the unreadable content. Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))"

    Now the macro is gone. Does anyone know why this happened or how I can fix it? I'd like to be able to keep using this macro without issues...
  • To post as a guest, your comment is unpublished.
    kristine · 5 years ago
    I love this macro! I want to modify the code to exclude certain sheets. Please help
  • To post as a guest, your comment is unpublished.
    Wahid · 5 years ago
    Work great, many thanks!!
  • To post as a guest, your comment is unpublished.
    Dave · 5 years ago
    How can I force an alpha sort in which lower case follows upper case?
    I tried a Custom List, which accomplished nothing, realized I hadn't selected Options >> Case Sensitive and tried again, with almost the same results.
    Only difference was that Excel sorted individual lower case entries before identical upper case ones.
    What I want is: ABCDE ... WXYZabcde ... wxyz

    Thanks!
  • To post as a guest, your comment is unpublished.
    adeoye · 5 years ago
    hi1 thanks for the code, does it take a long while to load after running the macro
  • To post as a guest, your comment is unpublished.
    Miguel · 5 years ago
    Thank you so much !! It worked great.
  • To post as a guest, your comment is unpublished.
    Sidrit Mero · 5 years ago
    You saved me 3600 seconds ! thank you !
  • To post as a guest, your comment is unpublished.
    Sam_Mel · 5 years ago
    Useful instructions...Thanks for adding value toward others knowledge.
  • To post as a guest, your comment is unpublished.
    rachel · 5 years ago
    thanks tons!!! fantastic macro! and such clear instructions!
  • To post as a guest, your comment is unpublished.
    Nes · 6 years ago
    Thank you so much, now i know what to do.. thank you very much
  • To post as a guest, your comment is unpublished.
    Gary Ho · 6 years ago
    Marco is fast and Awesome man. :lol:
  • To post as a guest, your comment is unpublished.
    Alka · 6 years ago
    Awesome!!!!worked out great!!!! :-)
  • To post as a guest, your comment is unpublished.
    Akshay · 6 years ago
    Thanks aton, sorted almost 100 sheets easily! :D
  • To post as a guest, your comment is unpublished.
    Vamsi · 6 years ago
    :lol:
    Sorting Macro worked perfectly, Thank you
  • To post as a guest, your comment is unpublished.
    Zahid · 6 years ago
    Thanks a lot
    very very nice
    once again thanks
  • To post as a guest, your comment is unpublished.
    Volkan · 6 years ago
    Thanks a lot !
    Very very useful tool.
  • To post as a guest, your comment is unpublished.
    Giang · 6 years ago
    Very nice :)
    Thanks a lot!!!
  • To post as a guest, your comment is unpublished.
    Kishore Joshi · 6 years ago
    worked like a charm. good work.
  • To post as a guest, your comment is unpublished.
    canuck74 · 6 years ago
    This saved me from having to manually sort worksheets for every company traded on American exchanges - more than 10,000 worksheets. Thanks
  • To post as a guest, your comment is unpublished.
    Madhav · 6 years ago
    Thanks a lot !!!!!!!!!!!!!
    Very very useful tool.
  • To post as a guest, your comment is unpublished.
    mahesh · 6 years ago
    hi, thanks, worked wonderfully to sort by macros in no time.
    much appreciate your tool.
  • To post as a guest, your comment is unpublished.
    GayeM · 6 years ago
    Macro worked perfectly. :-)
  • To post as a guest, your comment is unpublished.
    stevedoyle · 7 years ago
    Hi, I'm trying to run the macro but keep getting the error as follows:

    expected end of statement.

    The Macro is as follows:
    ysg2y
    VBA: Sort sheets order.

    12345678910111213141516171819 Sub SortWorkBook() 'Updateby20140624 Dim xResult As VbMsgBoxResult xTitleId = "KutoolsforExcel"xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId) For i = 1 To Application.Sheets.Count For j = 1 To Application.Sheets.Count - 1 If xResult = vbYes ThenIf UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) ThenSheets(j).Move after:=Sheets(j + 1) End IfElseIf xResult = vbNo ThenIf UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) ThenApplication.Sheets(j).Move after:=Application.Sheets(j + 1) End IfEnd IfNextNextEnd Sub
  • To post as a guest, your comment is unpublished.
    GaryE · 7 years ago
    Superb simple macro, worked a treat
  • To post as a guest, your comment is unpublished.
    yabyaban · 7 years ago
    Thank you very much in advance
  • To post as a guest, your comment is unpublished.
    Ricardo Pacheco · 7 years ago
    I was able to get to the last step.
    I pressed F5

    and got this error:

    Compile Error

    Expected End Sub
  • To post as a guest, your comment is unpublished.
    Gwen Banger · 7 years ago
    Thank you kindly - worked in seconds. Much appreciated - Take Care