Comment créer un sommaire automatique et généré en 1 clic sur Excel

Comment créer un sommaire qui se génère automatiquement sur EXCEL ? Dans ce tutoriel, nous allons voir comment créer rapidement un sommaire automatique qui va permettre d’accéder en un clic à l’ensemble des feuille de calcul d’un classeur.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

1. Présentation

Dans un précédent tutoriel, nous avions vu ensemble comment récupérer le nom de la feuille de calcul active au travers de deux méthodes :

• Tout d’abord en utilisant la formule CELLULE qui permet d’obtenir des informations sur la cellule dans laquelle celle-ci est insérée, mais également sur le document de travail. Nous avions alors vu qu’en retraitant les informations retournées, nous pouvions alors récupérer le nom de la feuille active,
• Puis dans un second temps, nous avions vu qu’en utilisant la puissance apportée par VBA nous pouvions également récupérer le nom de la feuille active

À la suite de ce tutoriel, vous avez été plusieurs à me demander comment faire pour récupérer le nom de toutes les feuilles, et non pas seulement celui de la feuille active.

Évidemment, cette opération va être un peu plus complexe et nous allons forcément devoir développer une petite macro-commande VBA pour y parvenir.

Mais rassurez-vous, cette macro-commande ne présente aucune difficulté particulière, y compris pour les personnes n’ayant jamais pratiqué le VBA.

2. Création de la macro-commande VBA

Pour commencer nous allons simplement créer une macro-commande VBA que nous allons appeler « creerSommaire » et que nous allons insérer dans un nouveau module du projet.

Pour tout savoir sur le VBA, je vous invite à suivre la formation gratuite offert sur Excelformation.fr, à laquelle vous accèderez en cliquant ici.

Voici la démarche pour créer la macro :

• Lancer l’éditeur de macro VBA en cliquant sur le bouton Visual Basic depuis l’onglet Développeur du ruban (voir comment activer l’onglet Développeur qui est masqué par défaut en suivant ce lien)

• Lorsque l’éditeur de code est lancé, nous pouvons créer un nouveau Module, en cliquant sur Insertion > Module :

• Et enfin, nous pouvons créer notre macro dans ce nouveau module

Sub creerSommaire()
End Sub 

3. Créer une nouvelle feuille de calcul pour le sommaire

Pour créer une nouvelle feuille de calcul dans un classeur, il suffit d’utiliser l’instruction Sheets.Add.

Par défaut, la cette nouvelle feuille est insérée juste avant la feuille de calcul active. Pour modifier ce comportement, il est possible d’utiliser les paramètres Before ou After en spécifiant après ou avant quelle feuille de calcul nous souhaitons que la nouvelle feuille de calcul se trouve.

S’agissant d’un sommaire, nous allons logiquement vouloir que celle-ci se retrouve au tout début du classeur :

 Sub creerSommaire()
    Sheets.Add before:=Worksheets(1)    ' Worksheets(1) correspond à la toute première feuille de calcul du classeur
End Sub 

Ensuite, pour identifier facilement la feuille de calcul dans laquelle nous allons insérer le sommaire, nous allons modifier son nom grâce à l’instruction ActiveSheet.Name :

    …
    ActiveSheet.Name = "SOMMAIRE"
     …

Et nous allons insérer un premier texte dans la cellule A1 :

    …
    [a1] = "SOMMAIRE DU CLASSEUR :"
     …

Pour tester le bon fonctionnement de la macro-commande, nous pouvons lancer celle-ci une première fois en cliquant sur le bouton d’exécution (ou en appuyant sur la touche [F5]) depuis l’éditeur de code :

Normalement, vous devriez avoir une nouvelle feuille de calculs portant le nom de « SOMMAIRE » et dans laquelle la cellule « A1 » a pour valeur « SOMMAIRE DU CLASSEUR : » :

À ce moment-là, si nous lançons la macro une seconde fois, nous allons avoir une erreur d’exécution :

Pas d’inquiétude, cette erreur est tout à fait normale. En effet, nous demandons à Excel de créer une nouvelle feuille, puis de la renommer en « SOMMAIRE », alors que nous avions déjà une feuille de calcul avec ce nom !

Il est donc nécessaire de supprimer au préalable cette feuille de calcul, en insérant le code suivant au tout début de la macro (avant la ligne Sheets.Add) :

    If Worksheets(1).Name = "SOMMAIRE" Then
        Application.DisplayAlerts = False
        Worksheets(1).Delete
        Application.DisplayAlerts = True
    End If 

Ce petit bout de code permet supprimer la première feuille de calcul du classeur lorsque celle-ci porte le nom de « SOMMAIRE ».

L’instruction Application.DisplayAlerts égale à false permet de ne pas afficher le message demandant la confirmation quant à la suppression de la feuille de calculs. Cela a pour effet de désactiver tous les messages d’alerte de VBA, nous pensons donc bien à les réactiver lorsque l’opération de suppression est accomplie.

Ne reste plus qu’à supprimer la nouvelle feuille créée par la macro (ici la feuille portant le nom de « Feuil2 ») afin que la feuille « SOMMAIRE » se retrouve en toute première position du classeur :

Et maintenant nous devrions pouvoir tester d’exécuter la macro plusieurs fois de suite, sans que cela ne pose le moindre problème !

4. Lister toutes les feuilles du classeur

Maintenant que notre macro-commande est prête et paramétrée, nous allons pouvoir nous attaquer à la construction du sommaire à proprement parler.

Dans un premier temps, nous allons voir comment lister toutes les feuilles de calculs pour récupérer leur nom, puis dans un second temps, nous verrons comment créer un lien hypertexte pour accéder aux feuilles de calculs concernées.

Pour récupérer le nom des feuilles de calculs, nous allons lister tous les objets Worksheet présents dans la collection d’objets Worksheets.

Un objet Worksheet représente une feuille de calcul du classeur.

Voici le code permettant de récupérer les noms de feuilles, vous trouverez les explications directement dans les commentaires :

    
    Dim ligne As Integer                ' Création d'une  variable pour sélectionner la ligne dans laquelle nous allons insérer le nom de  la feuille
    ligne = 3                           ' Nous commençons le  sommaire sur la ligne 3
    
    Dim sh As Worksheet                 ' Création d'un  objet Worksheet
    For Each sh In Worksheets           ' Nous listons  toutes les feuilles du classeur
        Cells(ligne, 1) = sh.Name       ' Nous affectons le  nom de la feuille en cours à la première cellule de la ligne en cours
        ligne = ligne + 1               ' Puis nous passons  à la ligne suivante
    Next                                ' Enfin, nous  pouvons fermer la boucle et passer à la feuille suivante 

Avant de passer à l’étape suivante, vous pouvez tester le code :

Si tout s’est bien passé, vous devriez retrouver le nom de chaque feuille de calcul directement dans la nouvelle feuille SOMMAIRE !

5. Création du lien hypertexte

À présent que nous savons comment récupérer les noms de chacune des feuilles de calcul, nous allons pouvoir simplement créer un lien hypertexte en VBA vers chacune d’elle afin de rendre le sommaire pleinement opérant.

Pour cela, deux possibilités s’offrent à nous :

• Soit créer un lien hypertexte directement dans la cellule,
• Soit utiliser la formule LIEN_HYPERTEXTE que nous avons déjà eu l’occasion de découvrir dans un précédent tutoriel

Pour plus de simplicité, nous allons utiliser la première solution.

Nous allons alors remplacer l’instruction qui permet de saisir le nom de la feuille dans la cellule en cours (Cells(ligne, 1) = sh.Name) par :

 …
ActiveSheet.Hyperlinks.Add anchor:=Cells(ligne, 1),
 Address:="", SubAddress:="'" & sh.Name &
 "'!A1", TextToDisplay:=sh.Name
…

Voici le détail de cette instruction :

• ActiveSheet.Hyperlinks.Add : Ici, nous créons un lien hypertexte dans la feuille de calcul active,
• anchor:=Cells(ligne, 1) : nous insérons ce lien dans la cellule en cours (situé sur la ligne retournée par la variable « ligne »),
• Address:="", SubAddress:="'" & sh.Name & "'!A1" : pour créer un lien vers une cellule du classeur, nous devons utiliser le paramètre SubAddress (le paramètre Address reste vide), en spécifiant le nom de la feuille entre guillemets simple pour le cas où la feuille possèderait un espace, puis nous spécifions les coordonnées de la cellule sur laquelle nous souhaitons que le lien atterrisse après avoir saisi un point d’exclamation,
• TextToDisplay:=sh.Name : et enfin, nous spécifions le texte que nous souhaitons afficher dans la cellule, ici le nom de la feuille

Enfin, n’oubliez pas d’enregistrer le fichier sous le format *.xlsm afin que celui-ci sauvegarde la macro-commande ! Dans le contraire, tout votre travail sera perdu…

Vous pouvez évidemment télécharger le fichier de ce cours en suivant le lien situé en haut de l’article.