Hoe maak je een inhoudsopgave voor alle werkbladen die automatisch bijwerkt?

Stel dat je een werkboek hebt dat honderden werkbladen bevat, het navigeren naar een specifiek werkblad onder meerdere werkbladen kan voor de meesten van ons hoofdpijn veroorzaken. In dit geval kan het maken van een inhoudsopgave voor deze werkbladen je helpen om snel en gemakkelijk naar een bepaald werkblad te gaan. Deze handleiding bespreekt hoe je een inhoudsopgave voor alle werkbladen maakt en de tabel automatisch bijwerkt wanneer je werkbladen invoegt, verwijdert of de namen ervan wijzigt.

Gebruik een formule om een inhoudsopgave voor alle werkbladen te maken die automatisch bijwerkt

Gebruik Kutools voor Excel om een inhoudsopgave voor alle werkbladen te maken die automatisch bijwerkt

Gebruik VBA-code om een inhoudsopgave voor alle werkbladen te maken die automatisch bijwerkt

Gebruik een formule om een inhoudsopgave voor alle werkbladen te maken die automatisch bijwerkt

In Excel kun je een inhoudsopgave maken door een celnaam te definiëren en de naam in een formule te verwijzen. Volg hiervoor de volgende stappen:

1. Voeg een nieuw werkblad in vóór alle werkbladen waar je de inhoudsopgave wilt maken en hernoem het zoals nodig.

2. Klik vervolgens op "Formule" > "Naam Definiëren", zie onderstaande schermafbeelding:

3. Voer in het dialoogvenster "Nieuwe Naam" een naam "Sheetlist" in het vak "Naam" in. Je kunt dit naar eigen wens aanpassen, en voer vervolgens de volgende formule in het tekstvak "Verwijst naar" in.

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

4. Klik vervolgens op de knop "OK" om het dialoogvenster te sluiten.

5. Ga nu naar het nieuwe werkblad waar je de inhoudsopgave wilt maken, en voer de volgende formule in een lege cel in:

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

6. Sleep vervolgens de vulgreep omlaag naar de cellen totdat lege cellen worden weergegeven, en alle bladnamen (inclusief de verborgen bladen) van het huidige werkboek zijn vermeld zoals in onderstaande schermafbeelding te zien is:

7. Maak vervolgens hyperlinks voor de inhoudsopgave met behulp van de volgende formule:

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

8. Nu, wanneer je op de hyperlinktekst klikt, ga je direct naar dat blad. En als je een nieuw blad invoegt, een blad verwijdert of de naam van een blad wijzigt, wordt de inhoudsopgave automatisch bijgewerkt.

Gebruik Kutools voor Excel om een inhoudsopgave voor alle werkbladen te maken die automatisch bijwerkt

Als je "Kutools voor Excel" hebt, kan de "Navigatiebalk" je helpen om alle bladnamen verticaal in een linkerpaneel weer te geven en snel en gemakkelijk naar een specifiek blad te navigeren.

Na het installeren van Kutools voor Excel, doe dan het volgende:

1. Klik op "Kutools" > "Navigatie", zie onderstaande schermafbeelding:

2. In het uitgevouwen "Navigatie"-paneel, klik op het pictogram "Werkboek & Blad", alle geopende werkboeken worden weergegeven in de bovenste lijstbox, en alle zichtbare bladen binnen het werkboek worden weergegeven in de onderste lijstbox, zie onderstaande schermafbeelding:

3. En nu kun je naar het blad gaan door op de bladnaam in het linkerpaneel te klikken. En wanneer je een blad verwijdert, invoegt of hernoemt, wordt de bladlijst in het paneel dynamisch bijgewerkt.

Gebruik VBA-code om een inhoudsopgave voor alle werkbladen te maken die automatisch bijwerkt

Soms hoef je de verborgen bladen niet weer te geven in de inhoudsopgave. Om dit probleem op te lossen, kan de volgende VBA-code je helpen.

1. Voeg een nieuw blad in vóór alle bladen waar je de inhoudsopgave wilt maken en hernoem het zoals nodig. Klik vervolgens met de rechtermuisknop op het bladtabblad en kies "Weergave Code" uit het contextmenu, zie onderstaande schermafbeelding:

2. Kopieer en plak in het geopende venster "Microsoft Visual Basic for Applications" de onderstaande code in het bladcodevenster:

VBA-code: Maak een inhoudsopgave voor alle werkbladen die automatisch bijwerkt

Private Sub Worksheet_Activate()
'Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
    If xWsh.Name <> xStrWShName Then
        If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
            Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
            Me.Range(xStrTCHeader).Offset(xI).Value = xI
            xI = xI + 1
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Druk vervolgens op de toets "F5" om deze code uit te voeren, en de inhoudsopgave wordt onmiddellijk gemaakt in het nieuwe werkblad. Alle verborgen bladen worden niet weergegeven in de inhoudsopgave, zie onderstaande schermafbeelding:

4. Vanaf nu, wanneer je bladen verwijdert, invoegt of hernoemt, wordt de inhoudsopgave dynamisch bijgewerkt.