Ga naar hoofdinhoud

Hoe maak ik een lijst met alle werkbladnamen vanuit een werkmap?

Stel dat u een werkmap heeft met meerdere werkbladen en nu wilt u alle bladnamen in de huidige werkmap vermelden, is er dan een snelle methode om een ​​lijst met deze bladnamen in Excel te maken zonder ze een voor een te typen? Deze tutorial geeft instructies voor het weergeven van werkbladnamen in Excel.

Haal dynamisch een lijst met alle werkbladnamen uit een werkmap met formules

Haal een lijst met alle werkbladnamen op uit een werkmap met een handige functie

Krijg een lijst met alle werkbladnamen uit een werkmap met VBA-code


Haal dynamisch een lijst met alle werkbladnamen uit een werkmap met formules

In Excel kunt u een bereiknaam definiëren en vervolgens een formule toepassen om alle bladnamen uit de huidige werkmap weer te geven, doe dit met de volgende stappen:

1. Ga om te klikken Formule > Name Manager, zie screenshot:

2. In de Name Manager dialoogvenster, klik New knop, zie screenshot:

3. In de pop-out Nieuwe naam dialoogvenster, specificeert u een naam in het Naam tekstvak en kopieer vervolgens de onderstaande formule naar het Verwijst naar tekstvak, zie screenshot:

=GET.WORKBOOK(1)&T(NOW())

4. Dan klikken OK > Sluiten om de dialoogvensters te sluiten, gaat u nu naar een blad waar u alle bladnamen wilt weergeven en voert u de volgende formule in een lege cel in:

=IFERROR(INDEX(MID(Sheetnames,FIND("]",Sheetnames)+1,255),ROWS($A$2:A2)),"")

Note: In de bovenstaande formule, Bladnamen is de bereiknaam die u in stap 3 hebt gekregen.

5. En sleep vervolgens de vulgreep naar de cellen wanneer lege cellen worden weergegeven, en nu zijn alle bladnamen van de huidige werkmap weergegeven zoals onderstaand screenshot:

6. Gebruik de onderstaande formule als u de hyperlink voor elk blad wilt maken:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

Note: In de bovenstaande formule, A2 is de cel die de bladnaam bevat, en A1 is de cel waarin u de actieve cel wilt plaatsen. Als u bijvoorbeeld op de hyperlinktekst klikt, wordt cel A1 van het blad gevonden.

7. Als u nu op de hyperlinktekst klikt, gaat u naar dat blad, zie onderstaande demo:

Tips:
  • 1. Met de bovenstaande formules worden de gemaakte bladnamen dynamisch weergegeven. Wanneer u de bladnaam in de werkmap wijzigt, wordt de indexbladnaam automatisch bijgewerkt.
  • 2. U moet het bestand opslaan als Excel-werkmap met ingeschakelde macro's formaat, als u wilt dat de formules goed werken nadat het bestand is gesloten en opnieuw geopend.

Haal een lijst met alle werkbladnamen op uit een werkmap met een handige functie

Met de Maak een lijst met bladnamen hulpprogramma van de invoegtoepassing van derden Kutools for Excelkunt u met één klik een lijst met werkbladnamen maken en naar elk werkblad linken met een hyperlink.

Opmerking:Om dit toe te passen Maak een lijst met bladnamen, ten eerste moet u het Kutools for Excelen pas de functie vervolgens snel en gemakkelijk toe.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Klik Koetools Plus > Werkblad > Maak een lijst met bladnamen, zie screenshot:

2. In de Maak een lijst met bladnamen dialoogvenster, specificeer dan de volgende instellingen:

(1.) Kies bladindexstijlen u wilt, kunt u de werkbladnamen maken met hyperlinks of met macroknoppen.

(2.) Voer de werkbladnamen in voor de bladindex.

(3.) Specificeer de locatie van de werkbladindex.

(4.) Specificeer hoeveel kolommen u in het nieuwe werkblad wilt gebruiken om de werkbladnamen weer te geven.

3. Nadat u de instellingen hebt voltooid, klikt u op OK. Alle werkbladnamen zijn weergegeven met koppelingen in een nieuw werkblad van de huidige werkmap. Zie screenshot:

werkbladnamen met hyperlinks werkbladnamen met macrotoetsen
Tip: Om deze functie te gebruiken, moet u installeren Kutools for Excel eerst, alsjeblieft klik om te downloaden en krijg een gratis proefperiode van 30 dagen .

Krijg een lijst met alle werkbladnamen uit een werkmap met VBA-code

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

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

VBA: maak een lijst van alle werkbladnamen met hyperlinks in een nieuw werkblad:

Sub CreateIndex()
'updateby Extendoffice
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3. druk de F5 toets om deze macro uit te voeren. Nu zijn alle werkbladnamen in de actieve werkmap opgenomen in een nieuw werkblad met de naam Index, en de bladnamen zijn ook aan elk blad gekoppeld, zie screenshot:

Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
First, thanks to the author. And adding the Czech version.

tp 3. =O.KNIZE(1)&T(NYNÍ())
tp 4. =IFERROR(INDEX(ČÁST(nazvylistu;NAJÍT("]";nazvylistu)+1;255);ŘÁDKY($A$2:A4));"")
This comment was minimized by the moderator on the site
Hello,

je viens de tester la méthode via macros (dynamique avec formules) et ça marche au poil donc je tiens vraiment à remercier l'auteur de cet article parce que ça va vraiment m'aider dans mon travail ! Juste, je me permet de corriger les formules pour la version française. Et alors je ne sais pas si c'est parce que je suis sous la version 2019 mais Excel rouspète quand il n'y a pas d'argument en 3ème position de la fonction STXT ("MID" en version anglaise) donc obligé d'en rajouter un. Donc voilà ce que ça donne :

=LIRE.CLASSEUR(1)&T(MAINTENANT())

=SIERREUR(INDEX(STXT(nomsFeuilles;TROUVE("]";nomsFeuilles)+1,255;20);LIGNES($A$2:A2));"")

Bon travail à tous ! ;-)
This comment was minimized by the moderator on the site
Hello, Gizmil
Thank you for your comment, there are some functions are only available for English in Excel.
Your formula may help others.
Thanks again!
This comment was minimized by the moderator on the site
I close and open my document and all values in my sheet names column are all gone and blank but still the formula is there. I tried entering the same formula but it doesn't show the value anymore
This comment was minimized by the moderator on the site
Hello, Anne,
Sorrry for replying late, after creating the range names and formulas, you should save the workbook as Excel Macro-Enabled Workbook format, so next time, when you open the Excel file,the formulas can work well.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
I tried this one and it works. But when I close and open the file again all the values in my sheet names are blank and gone but the formula is still there. I tried enteing the same formula again but it doesn't show the value anymore
This comment was minimized by the moderator on the site
BRILLIANT!! Thank you so much! 😊
This comment was minimized by the moderator on the site
Causes problems when document protection is enabled by email or corporate policy
This comment was minimized by the moderator on the site
Thanks so much, this worked great.
This comment was minimized by the moderator on the site
Works great!! Thank you!!!!
This comment was minimized by the moderator on the site
sooooo helpful, works as expected!!!!! Thanks
This comment was minimized by the moderator on the site
THANK YOU SO MUCH! I freaking love your website. In a matter of minutes I've had a ton of time saved with two sections of this site including this one. Love it!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations