Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

Comment créer une liste dynamique de noms de feuille 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 feuille dans une nouvelle feuille dans le classeur, comment pouvez-vous faire? Maintenant, ce tutoriel introduit quelques astuces pour compléter rapidement cette mission dans Excel.

Créer une liste dynamique des noms de feuille de calcul avec Définir le nom et la formule

Créer une liste dynamique de noms de feuille de calcul avec le code VBA

Créer une liste dynamique des noms de feuille de calcul avec Kutools pour Excel bonne idée3

Afficher une liste dynamique des noms de feuille de calcul avec Kutools for Excel bonne idée3


Volet de navigation - Workbook & Sheet (voir tous les classeurs ouverts sont la liste, et toutes les feuilles
du classeur actuel sont liste dans le volet, vous pouvez cliquer sur le nom de la feuille pour y accéder.)

doc liste de navigation
Onglet Office Activer l'édition et la navigation par onglets dans Office, et simplifiez grandement votre travail ...
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%
  • Réutiliser n'importe quoi: Ajoutez les formules, graphiques et autres éléments les plus utilisés ou les plus complexes à vos favoris et réutilisez-les rapidement.
  • Plus que le texte 20 comprend: Extraire le numéro de la chaîne de texte; Extraire ou supprimer une partie des textes; Convertissez les chiffres et les devises en mots anglais ...
  • Fusionner les outils: Plusieurs classeurs et feuilles en un; Fusionner plusieurs cellules / lignes / colonnes sans perdre de données; Fusionner des lignes en double et somme ...
  • Outils Split: Fractionner les données en plusieurs feuilles en fonction de la valeur; Un classeur pour plusieurs fichiers Excel, PDF ou CSV; Une colonne à plusieurs colonnes ...
  • Coller Sauter Lignes cachées / filtrées; Compte et somme par couleur de fond; Créer une liste de diffusion et Envoyer des courriels par valeur de cellule...
  • Super filtre: Créez des schémas de filtrage avancés et appliquez-les à toutes les feuilles. Trier par semaine, jour, fréquence et plus; Filtre en gras, formules, commentaires ...
  • Plus que de puissantes fonctionnalités 300; Fonctionne avec Office 2007-2019 et 365; Prend en charge toutes les langues; Déploiement facile dans votre entreprise ou organisation.


1. Sélectionnez une cellule dans une feuille vierge, ici je choisis A1, puis cliquez sur Formules > Définir le nom. voir capture d'écran:
doc-dynamic-worksheetname-list-1

formule Ne plus jamais avoir à vous soucier des longues formules longues dans Excel! Kutools for Excel's Texte automatique peut ajouter toutes les formules à un groupe
en tant que texte automatique, et libérez votre cerveau! Cliquez ici pour connaître le texte automatique Cliquez ici pour obtenir un essai gratuit

2. Puis dans le Nouveau nom dialogue, tapez Feuilles dans le Nom textbox (vous pouvez changer comme vous avez besoin), et tapez cette formule = SUBSTITUTE (GET.WORKBOOK (1), "[" & GET.WORKBOOK (16) & "]", "") dans la zone de texte Refers to. Voir la capture d'écran:
doc-dynamic-worksheetname-list-2

3. Cliquez OK. Allez dans la cellule sélectionnée (A1) et tapez cette formule = INDEX (Sheets, ROWS ($ A $ 1: $ A1)) (A1 est la cellule que vous tapez cette formule, "Sheets" est le nom que vous avez défini à l'étape 2), puis faites glisser la poignée de remplissage automatique vers le bas jusqu'à #REF! apparaît.
doc-dynamic-worksheetname-list-3

Pointe: Si des feuilles de calcul sont supprimées ou ajoutées, vous devez accéder à A1et appuyez sur la touche Entrée, puis raglez à nouveau la poignée de saisie automatique.
doc-dynamic-worksheetname-list-4


Si vous souhaitez créer une liste dynamique de noms de feuille de calcul pouvant être liés à chaque feuille, vous pouvez utiliser le code VBA.

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

doc-dynamic-worksheetname-list-5 doc-dynamic-worksheetname-list-6

2. Faites un clic droit sur le nom de la feuille d'index, sélectionnez Voir le code à partir du menu contextuel. Voir la capture d'écran:
doc-dynamic-worksheetname-list-7

3. Dans la fenêtre contextuelle, copiez et collez le code VBA ci-dessous.

VBA: Créer une liste dynamique des noms de feuille 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 course or F5 Pour exécuter le VBA, une liste dynamique de noms de feuille de calcul est créée.
doc-dynamic-worksheetname-list-8

Pointe:

1. Lorsque la feuille de calcul du classeur est supprimée ou insérée, la liste des noms de feuille de calcul change automatiquement.

2. Vous pouvez cliquer sur le nom de la feuille de la liste des noms pour aller à la feuille.

Avec deux méthodes ci-dessus ne sont pas assez convaincantes, si vous êtes intéressé par un nouvel utilitaire, vous pouvez aller deux méthodes qui peuvent rendre cette tâche plus facile.


Si vous souhaitez uniquement afficher rapidement tous les noms de feuille de calcul du classeur et les lier aux feuilles d'origine, vous pouvez utiliser Kutools for Excel's Créer une liste de noms de feuille.

Kutools for Excel, avec plus de 120 fonctions Excel pratiques, améliorez votre efficacité de travail et économisez votre temps de travail.

Après installation gratuite Kutools pour Excel, s'il vous plaît faire comme ci-dessous:

1. Cliquez Entreprise > Feuille > Créer une liste de noms de feuille. Voir la capture d'écran:

doc nom de la feuille de calcul dynamique 17

2. dans le Créer une liste de noms de feuille dialogue:

doc-dynamic-worksheetname-list-10
(1) Vérifiez le style d'index dont vous avez besoin Styles d'index de feuille section;
(2) Nommez la nouvelle feuille d'index en tapant les données dans Indiquez le nom de la feuille pour l'index de feuille zone de texte;
(3) Spécifiez l'emplacement où vous placerez la feuille d'index ajoutée dans le Insérer l'index de la feuille dans liste;
(4) Si vous souhaitez afficher les noms de feuille dans une seule liste, sélectionnez la colonne 1 dans Affichage de l'index de la feuille dans liste.

3. Cliquez Ok. Maintenant, vous pouvez voir les noms des feuilles sont répertoriés.
doc-dynamic-worksheetname-list-11

Conseils:

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

2. Les noms de liste ou de feuille ne peuvent pas changer dynamiquement avec les feuilles insérant ou supprimant.

3. En fait, vous pouvez également créer une liste de bouton pour lier la feuille relative, juste besoin de vérifier Contient des boutons et des macros dans le dialogue. voir screeshshot:
doc nom de la feuille de calcul dynamique 16

Cliquez ici pour en savoir plus sur Créer une liste de noms de feuilles.


Si vous avec Kutools for Excel, vous pouvez également utiliser le Menu utilitaire pour afficher les noms de feuille de travail liables dans un volet

Après installation gratuite Kutools pour Excel, s'il vous plaît faire comme ci-dessous:

1. Cliquez Kutools > Menu. Voir la capture d'écran:
doc-dynamic-worksheetname-list-12

2. Ensuite, un volet apparaît dans la feuille active, cliquez sur Cahier de travail et feuille Pour afficher le classeur et la feuille de calcul et vous pouvez sélectionner un classeur, ses feuilles de calcul s'affichent dans le classeur. Menu vitre. Voir les captures d'écran:

doc-dynamic-worksheetname-list-13 doc-dynamic-worksheetname-list-14

Pointe:

Lorsque des feuilles de calcul sont supprimées ou ajoutées, vous pouvez cliquer sur bouton d'actualisation le Navigation volet pour actualiser les noms de feuille de calcul.
doc-dynamic-worksheetname-list-15

Cliquez ici pour plus d'informations sur la navigation.


Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe Créer une liste de diffusion et envoyer des emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit le nombre de clics de souris pour vous chaque jour!
fond officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Kelly · 1 years ago
    Thanks for this helpful info. I created a dynamic list with VBA code in the Index tab. I found that it only updates when I click back to the Index tab, though. Is there a way to ensure it auto updates as soon as a new tab is created? I refer to the tab names elsewhere in the workbook and others will be using it so I'm trying to make it seamless. Thank you!
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Sorry, I do not find that code can finish your job.
  • To post as a guest, your comment is unpublished.
    Ed Bower · 1 years ago
    Or you can simply rightClick either the left or right tab-horizontal-scroll arrow at the bottomLeft, and then click the worksheet name (from the simple vertical list) that you want to jump to.
  • To post as a guest, your comment is unpublished.
    PB · 1 years ago
    Just what I needed and worked perfectly. Thank you!
  • To post as a guest, your comment is unpublished.
    KP · 2 years ago
    Defining the name "Sheets" in the define name DOES NOT WORK. The "refers to" box will not save the formula as typed and converts it to a text string.
  • To post as a guest, your comment is unpublished.
    j4h54n84 · 2 years ago
    how can I use this code to create the list of worksheets in horizontal order instead of vertical? is it possible?
  • To post as a guest, your comment is unpublished.
    Cindy · 2 years ago
    Thank you. It is very useful. :D
  • To post as a guest, your comment is unpublished.
    Nash · 2 years ago
    How can i remove the links to each sheets and how to not include the "sheet1" and "Index" sheet?

    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
  • To post as a guest, your comment is unpublished.
    Matt · 3 years ago
    How would I go about editing this code to include a checkbox to the left of the link that I could use to hide rows on a cover sheet? Also how could I insert a row on the worksheets below A1 to have the return to index link show up there?