Excel : Créer automatiquement la liste des noms d’onglets d’un fichier

Publié le 25 juin 2013
par Sophie Marchand M.Sc.
Excel Macro Liste Onglets

Lorsque vous élaborez un modèle financier dans Excel, il peut s’avérer intéressant de créer une liste des noms d’onglets du fichier. Cela peut être utile notamment lorsque vous souhaitez créer un sommaire par onglet avec une formule du type Indirect, tel que démontré dans l’article: Excel: La fonction Indirect pour créer des sommaires exécutifs en un clic de souris. Cela peut être utile également lorsque vous créez une table des matières ou une liste de pages à imprimer dans votre modèle. Quelque soit la raison qui vous incite à créer une liste avec le nom des onglets de votre modèle, vous devrez nécessairement utiliser une macro. Aucune fonction intégrée dans Excel ne permet de dresser une telle liste.

 

Rédiger une macro VBA pour créer la liste des noms d’onglets

 

Écrire la macro

Si vous n’êtes pas un habitué des macros VBA, veuillez simplement vous rendre dans votre fichier Excel et cliquer sur ALT+F11. Ensuite, cliquez sur “Insert” et sur “Module”.

Macro Excel Liste Onglets

 

C’est ici que vous inscrirez le code suivant:

Sub Snamelist()
Dim i As Integer

    Range("B5").Select

    For i = 1 To Sheets.Count
        ActiveCell.Value = Sheets(i).Name
        ActiveCell.Offset(1, 0).Select
    Next i

End Sub

Excel Macro Liste Onglets

 

Vous pourrez ensuite retourner dans Excel et cliquer sur “View Macros” dans le menu “View”/”Macros”.

Excel Macro Liste Onglets

 

Vous souhaitez créer et personnaliser des outils de gestion automatisés dans Excel  pour mieux répondre aux besoins spécifiques de votre entreprise ? Apprenez à les développer et les présenter avec des interfaces conviviales pour les usagers en suivant nos formations en programmation de macros VBA.

 

Activer la macro

En choisissant votre macro (dans notre exemple, “Snamelist”) et en cliquant sur “Run”, vous activerez la macro et Excel dressera donc la liste des onglets de votre fichier.

Excel Macro Liste Onglets

 

Excel Macro Liste Onglets

 

Vous pourriez également choisir d’insérer un bouton et d’attacher la macro à votre bouton.

Macro_liste_nom_onglets_6

 

Excel Macro Liste Onglets

 

Obtenir la liste des noms d’onglets du fichier

Vous obtiendrez ainsi, à partir de la cellule B5 de votre onglet actif, la liste de tous les noms des onglets de votre fichier.

 


Formation complémentaire

Pour une introduction à la programmation avec le langage VBA, qui permet de sauver du temps et de réduire les risques d’erreurs en automatisant certaines tâches et certains processus, afin de développer des solutions robustes dans Excel, suivez la formation Excel – VBA (niveau 1).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Excel – VBA (niveau 1)

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

7 réflexions sur “Excel : Créer automatiquement la liste des noms d’onglets d’un fichier”

  1. Frédéric WILMOUTH

    Concernant cet article, j’ai une autre proposition à vous faire qui permet une actualisation automatique sans action particulière de la part de l’utilisateur.

    Il est encore possible, à certains endroits et même dans les versions les plus récentes d’Excel, d’utiliser les macros Excel 4 (qui n’étaient pas en VBA).
    Pour l’exemple qui nous intéresse, il suffit de définir un nouveau nom (“Onglets” par exemple) auquel on associe la formule suivante : “=LIRE.CLASSEUR(1+0*MAINTENANT())” (la fonction “MAINTENANT()” n’est là que pour permettre l’actualisation de la formule).
    On peut, ensuite, facilement utiliser une formule dans une des feuilles du classeur du type “=SI(LIGNE(1:1)>NBVAL(Onglets);””;INDEX(Onglets;LIGNE(1:1)))” qu’il suffit de recopier vers le bas pour obtenir la liste de tous les onglets du classeur (on peut, bien sûr, modifier la formule pour obtenir exactement le même résultat que dans votre article). J’ai trouvé cette astuce dans le livre “Magique Excel” aux éditions Pearson Education.

    1. A priori ça ne met pas à jour, mais l’astuce est sympa 🙂 (et ça me dit #NOM?)il faut que je clique dans les cellules pour revalider la formule.
      De plus, je n’arrive pas à rendre les liens “hypertexte”. 🙁

    2. Bonjour,
      “il suffit de” n’apporte aucune information et dévalorise celui qui n’y arrive pas.
      Mettre “il faut” ou “vous devez” ou mieux, ne pas ajouter cette locution.
      Ainsi la phrase devient plus simple.
      Le fait de l’écrire deux fois devient gênant dans une explication mais, tous les informaticiens… le font !

  2. J’ai une question. Supposez que dans la colonne B, j’ai des noms sur la plage B2:B118 (B2=Cameroun, B3=France,…, B118=Zambie) et je désire créer automatiquement des feuilles dans le classeur avec les noms de cette plage (Cameroun, France…). Vous avez une recette ? Merci d’avance

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut