Comment créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement ?

Supposons que vous ayez un classeur contenant des centaines de feuilles de calcul. Naviguer vers une feuille spécifique parmi plusieurs peut poser problème pour la plupart d'entre nous. Dans ce cas, créer une table des matières pour ces feuilles peut vous aider à accéder rapidement et facilement à une feuille donnée. Ce tutoriel expliquera comment créer une table des matières pour toutes les feuilles et mettre à jour la table automatiquement lors de l'insertion, la suppression ou le changement de noms des feuilles.

Utiliser une formule pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement

Utiliser Kutools pour Excel pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement

Utiliser du code VBA pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement

Utiliser une formule pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement

Dans Excel, vous pouvez créer une table des matières en définissant un nom de plage et en référençant ce nom dans une formule. Suivez les étapes ci-dessous :

1. Insérez une nouvelle feuille avant toutes les autres feuilles où vous souhaitez créer la table des matières et renommez-la selon vos besoins.

2. Ensuite, cliquez sur "Formule" > "Définir un nom", voir capture d'écran :

3. Dans la boîte de dialogue "Nouveau nom", spécifiez un nom "Sheetlist" dans la zone "Nom", vous pouvez le changer à votre convenance, puis entrez la formule suivante dans la zone de texte "Fait référence à".

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

4. Ensuite, cliquez sur le bouton "OK" pour fermer la boîte de dialogue.

5. Maintenant, rendez-vous sur la nouvelle feuille où vous souhaitez créer une table des matières, puis entrez la formule suivante dans une cellule vide :

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

6. Ensuite, faites glisser la poignée de recopie vers le bas jusqu'à ce que des cellules vides apparaissent, et tous les noms de feuilles (y compris les feuilles masquées) du classeur actuel seront listés comme indiqué dans la capture d'écran ci-dessous :

7. Ensuite, vous devez créer des hyperliens pour le contenu de la table. Utilisez la formule ci-dessous :

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

8. Maintenant, lorsque vous cliquez sur le texte du lien hypertexte, vous serez immédiatement redirigé vers cette feuille. Et si vous insérez une nouvelle feuille, supprimez une feuille ou changez le nom de la feuille, le contenu de la table sera mis à jour automatiquement.

Utiliser Kutools pour Excel pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement

Si vous disposez de "Kutools pour Excel", son "Panneau de navigation" peut vous aider à lister tous les noms de feuilles dans un volet vertical à gauche, et à naviguer rapidement et facilement vers une feuille spécifique.

Après avoir installé Kutools pour Excel, procédez comme suit :

1. Cliquez sur "Kutools" > "Navigation", voir capture d'écran :

2. Dans le volet "Navigation" développé, cliquez sur l'icône "Classeur et Feuille", tous les classeurs ouverts sont listés dans la zone de liste en haut, et toutes les feuilles visibles du classeur sont affichées dans la zone de liste en bas, voir capture d'écran :

3. Et maintenant, vous pouvez accéder à la feuille simplement en cliquant sur le nom de la feuille dans le volet de gauche. Et lorsque vous supprimez, insérez ou renommez une feuille, la liste des feuilles dans le volet sera mise à jour dynamiquement.

Utiliser du code VBA pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement

Parfois, vous n'avez pas besoin d'afficher les feuilles masquées dans le contenu de la table. Pour résoudre ce problème, le code VBA suivant peut vous aider.

1. Insérez une nouvelle feuille avant toutes les autres feuilles où vous souhaitez créer la table des matières et renommez-la selon vos besoins. Ensuite, faites un clic droit sur l'onglet de la feuille et choisissez "Afficher le code" dans le menu contextuel, voir capture d'écran :

2. Dans la fenêtre "Microsoft Visual Basic for Applications" ouverte, copiez et collez le code ci-dessous dans la fenêtre de code de la feuille :

Code VBA : Créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement

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. Ensuite, appuyez sur la touche "F5" pour exécuter ce code, et le contenu de la table est créé immédiatement dans la nouvelle feuille de calcul, toutes les feuilles masquées ne seront pas affichées dans le contenu de la table, voir capture d'écran :

4. Désormais, lors de la suppression, insertion ou renommage de feuilles, le contenu de la table sera mis à jour dynamiquement.