Comment créer une liste dynamique de noms de feuilles de calcul dans Excel ?

Si vous avez plusieurs feuilles dans un classeur et que vous souhaitez créer une liste dynamique de tous les noms de feuilles dans une nouvelle feuille du classeur, comment pouvez-vous procéder ? Ce tutoriel présente quelques astuces pour accomplir rapidement cette tâche dans Excel.

Créer une liste dynamique de noms de feuilles de calcul avec le Nom Défini et la Formule

Créer une liste dynamique de noms de feuilles de calcul avec du code VBA

Créer une liste dynamique de noms de feuilles de calcul avec Kutools pour Excel

Afficher une liste dynamique de noms de feuilles de calcul avec Kutools pour Excel

Créer une liste dynamique de noms de feuilles de calcul avec le Nom Défini et la Formule

1. Sélectionnez une cellule dans une feuille vierge, ici je sélectionne A1, puis cliquez sur Formules > Définir un nom. voir capture d'écran :

2. Ensuite, dans la boîte de dialogue Nouveau nom saisissez Feuilles dans le champ Nom (vous pouvez le modifier selon vos besoins), puis saisissez cette formule =SUBSTITUE(OBTENIR.CARNET(1),"["&OBTENIR.CARNET(16)&"]","") dans le champ Fait référence à. Voir capture d'écran :

3. Cliquez sur OK. Allez ensuite dans la cellule sélectionnée (A1) et saisissez cette formule =INDEX(Feuilles,LIGNES($A$1:$A1)) (A1 est la cellule où vous saisissez cette formule, "Feuilles" est le nom défini à l'étape 2) puis faites glisser la poignée de recopie automatique vers le bas jusqu'à ce que #REF! apparaisse.

Astuce: Si des feuilles de calcul sont supprimées ou ajoutées, vous devez retourner en A1, appuyer sur Entrée, puis faire glisser à nouveau la poignée de recopie.

Créer une liste dynamique de noms de feuilles de calcul avec du code VBA

Si vous souhaitez créer une liste dynamique de noms de feuilles de calcul qui peuvent être liées à chaque feuille, vous pouvez utiliser du code VBA.

1. Créez une nouvelle feuille de calcul et renommez-la Index. Voir capture d'écran :

2. Cliquez avec le bouton droit sur le nom de la feuille Index, puis sélectionnez Afficher le code à partir du menu contextuel. Voir capture d'écran :

3. Dans la fenêtre qui s’affiche, copiez et collez le code VBA ci-dessous.

VBA : Créer une liste dynamique de noms de feuilles de calcul.

Private Sub Worksheet_Activate()
	'Updateby20150305
	Dim xSheet As Worksheet
	Dim xRow As Integer
	Dim calcState As Long
	Dim scrUpdateState As Long
	Application.ScreenUpdating = False
	xRow                       = 1
	With Me
		.Columns(1).ClearContents
		.Cells(1, 1) = "INDEX"
		.Cells(1, 1).Name = "Index"
	End With
	For Each xSheet In Application.Worksheets
		If xSheet.Name <> Me.Name Then
			xRow                     = xRow + 1
			With xSheet
				.Range("A1").Name = "Start_" & xSheet.Index
				.Hyperlinks.Add anchor: = .Range("A1"), Address: = "", _
				SubAddress:             = "Index", TextToDisplay: = "Back to Index"
			End With
			Me.Hyperlinks.Add anchor: = Me.Cells(xRow, 1), Address: = "", _
			SubAddress: = "Start_" & xSheet.Index, TextToDisplay: = xSheet.Name
		End If
	Next
	Application.ScreenUpdating = True
End Sub

4. Cliquez sur Exécuter ou appuyez sur F5 pour exécuter le VBA ; maintenant, une liste dynamique de noms de feuilles de calcul est créée.

Astuce :

1. Lorsque des feuilles de calcul sont supprimées ou insérées dans le classeur, la liste des noms de feuilles se mettra automatiquement à jour.

2. Vous pouvez cliquer sur le nom de la feuille dans la liste pour accéder à cette feuille.

Les deux méthodes ci-dessus ne sont pas suffisamment pratiques. Si vous êtes intéressé par une nouvelle utilité, vous pouvez passer aux deux méthodes suivantes qui rendront cette tâche plus facile.

Créer une liste dynamique de noms de feuilles de calcul avec Kutools pour Excel

 Si vous souhaitez simplement lister rapidement tous les noms de feuilles de calcul du classeur et les lier aux feuilles d'origine, vous pouvez utiliser l'utilitaire Créer une table de liens de Kutools pour Excel.

Après avoir installé gratuitement Kutools pour Excel, veuillez procéder comme suit :

1. Cliquez sur Kutools Plus > Feuille de calcul > Créer une table de liens. Voir capture d'écran :

2. Dans la boîte de dialogue Créer une table de liens :

 

(1) Cochez le style d'index dont vous avez besoin dans la section Style ;
(2) Nommez la nouvelle feuille d'index en saisissant les données dans la zone de texte Nom de la table de liens ;
(3) Spécifiez l'emplacement où vous placerez la feuille d'index ajoutée dans la liste Emplacement dans le classeur ;
(4) Si vous souhaitez afficher les noms de feuilles dans une seule liste, sélectionnez 1 ligne dans la liste Nombre de lignes à fusionner .

3. Cliquez sur Ok. Maintenant, vous pouvez voir que les noms des feuilles sont listés.

Astuce :

1. Vous pouvez cliquer sur le nom de la feuille pour accéder rapidement à sa feuille d'origine.

2. La liste des noms de feuilles ne peut pas changer dynamiquement avec l'insertion ou la suppression de feuilles.

3. En réalité, vous pouvez également créer une liste de boutons pour lier aux feuilles correspondantes, il suffit de cocher Créer des boutons et des macros dans la boîte de dialogue. Voir capture d'écran :

Cliquez ici pour en savoir plus sur Créer une table de liens.

  Créer une liste de noms de feuilles cliquables

Afficher une liste dynamique de noms de feuilles de calcul avec Kutools pour Excel

Avec Kutools pour Excel, vous pouvez également utiliser l'utilitaire Navigation pour afficher les noms de feuilles de calcul accessibles via un volet.

Après installé gratuitement Kutools pour Excel, veuillez procéder comme suit :

1. Cliquez sur Kutools > Navigation. Cliquez sur Classeur & Feuille de calcul pour afficher le classeur et les feuilles de calcul, et vous pouvez sélectionner un classeur, puis ses feuilles de calcul seront affichées dans le volet Navigation . Voir capture d'écran :
   

Astuce :

Lorsque des feuilles de calcul sont supprimées ou ajoutées, vous pouvez cliquer sur le bouton Actualiser  dans le volet Navigation pour actualiser les noms des feuilles de calcul.

Cliquez ici pour plus d'informations sur la Navigation.

  Volet de navigation -- Liste des feuilles