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”.
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
Vous pourrez ensuite retourner dans Excel et cliquer sur “View Macros” dans le menu “View”/”Macros”.
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.
Vous pourriez également choisir d’insérer un bouton et d’attacher la macro à votre bouton.
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 :
La 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é.
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.
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”. 🙁
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 !
Merci pour le tuto. Super pour que je retrouve mes recettes.
Vous m’avez fait gagner un temps énorme !!!!
Merci beaucoup.