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

or

Hoe werkbladen in alfabetische / alfanumerieke volgorde in Excel te sorteren?

Normaal gesproken kunt u de volgorde van werkbladtabs in Excel sorteren of rangschikken door de bladtabs op de werkbladtabbalk te slepen en neer te zetten. Maar om dit voor elkaar te krijgen met meerdere werkbladen, kunt u de volgende lastige manieren overwegen om werkbladen snel in alfabetische / alfanumerieke volgorde in een grote werkmap te sorteren.

Sorteer werkbladen in alfabetische / alfanumerieke volgorde met VBA-code
Sorteer werkbladen in alfabetische / alfanumerieke volgorde met Kutools voor Excel


Sorteer werkbladen in alfabetische / alfanumerieke volgorde met VBA-code

Er is een macro voor het sorteren van werkbladen op alfa, gepost in het Microsoft Support Center. We kunnen het toepassen met de volgende stappen:

1.  Houd de toets ingedrukt ALT + F11 toetsen, en het opent de Microsoft Visual Basic voor toepassingen venster.

2.  Klik Invoegen > Moduleen plak de volgende macro in het Module Venster.

VBA: Sorteer bladen in alfabetische / alfanumerieke volgorde

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. druk de F5 toets om deze macro uit te voeren. Klik in het volgende promptvenster op Ja, alle werkbladen worden in oplopende alfabetische volgorde gesorteerd; en klik Neeworden alle werkbladen gesorteerd in aflopende alfabetische volgorde.


Sorteer werkbladen in alfabetische / alfanumerieke volgorde met Kutools voor Excel

Als u niet bekend bent met macro's of de voorkeur geeft aan andere manieren, kunt u het proberen Kutools for Excel. Kutools for Excel's Sorteer bladen tool kan alle werkbladen gemakkelijk sorteren.

Voor het aanvragen Kutools for Excel, Dan kunt u download en installeer het eerst.

1. Klikken Kutools Plus > Werkblad > Sorteer bladen. Zie screenshot:

2. In de Sorteer bladen dialoogvenster, selecteert u een sorteringstype dat u nodig hebt in het rechterdeelvenster, zoals Alpha Sorteren, Alfanumeriek sorterenen klik vervolgens op de OK knop. Zie screenshot:

Vervolgens worden alle werkbladen gesorteerd op basis van het opgegeven sorteertype. Zie screenshot:

doc-sort-sheets6

Kutools for Excel's Sorteer bladen tool kan snel alle werkbladen in de actieve werkmap herschikken. Het ondersteunt verschillende sorteringstypen, waaronder Alpha Sorteren, Alfanumeriek sorteren, Kleur sorteren als Omkeren. Bovendien kunt u ook werkbladen omhoog / omlaag verplaatsen en de sortering opnieuw instellen.

  Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.


Demo: Sorteer alle werkbladen in alfabetische / alfanumerieke volgorde


Gerelateerd artikel:

Sorteer werkbladtabbladen op kleur


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
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 · 25 days 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 · 3 years ago
    Thanks a Lot!!!
  • To post as a guest, your comment is unpublished.
    Chris Hall · 3 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 · 4 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 · 5 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