Découvrez comment diviser un classeur Excel en un rien de temps ?

Dans ce tutoriel, je vais vous montrer comment diviser un classeur Excel en une multitude de feuilles de calculs.

Que ce soit pour mieux organiser vos données ou pour simplifier la navigation dans notre document, cette astuce est indispensable pour tout utilisateur d'Excel.

Pour y parvenir, nous découvrirons deux méthodes : la première consiste à le faire manuellement, feuille par feuille, et la seconde consiste à utiliser une macro commande en VBA obtenir le même résultat d’un simple clic.

Téléchargement

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

Tutoriel Vidéo

1. Division manuelle d'un classeur en feuilles de calculs

   1.1. Exporter toutes les feuilles dans des classeurs indépendants

Pour diviser manuellement un classeur en feuilles de calculs, la méthode la plus simple consiste à exporter toutes les feuilles dans des classeurs indépendants.

Pour cela, nous pouvons suivre les étapes suivantes :

• Pour commencer, nous sélectionnons la première du classeur que nous souhaitons exporter,
• Puis, avec le bouton droit de la souris, nous cliquons sur l'onglet de la feuille afin de sélectionner "Déplacer ou copier".

• Sur la boîte de dialogue qui s’affiche, nous commençons par cocher la case "Créer une copie" pour éviter de perdre les données originales du classeur initial. En effet, en cochant cette case, Excel créera une copie de la feuille sélectionnée dans le classeur de destination, alors que dans le cas contraire Excel se contentera de déplacer la feuille sélectionnée vers celui-ci et la supprimera du classeur initial, pouvant occasionner la perte de données.
• Sur la même boîte de dialogue, nous allons également sélectionner "Nouveau classeur" depuis la liste déroulante « Dans le classeur », afin qu’Excel génère un nouveau classeur avec la feuille sélectionnée

• Ensuite, nous cliquons sur le bouton "OK" générer le nouveau classeur.
• Il ne reste plus qu’à enregistrer ce nouveau classeur à l’endroit souhaité avec le nom voulu

Maintenant que nous avons exporté la première feuille du classeur, il nous restera encore à répéter cette même opération pour toutes les feuilles du classeur d’origine.

   1.2. Avantages / Inconvénients de la méthode

Comme vous avez pu le constater, cette méthode est simple à mettre en place, mais elle peut être fastidieuse si vous avez de nombreuses feuilles à exporter.

Il peut en effet arriver que nous ayons à diviser un classeur contenant de très nombreuses feuilles (plusieurs dizaine ou même plusieurs centaines…).

Elle peut également être source d'erreurs si vous oubliez d'exporter une feuille importante.

2. Division automatique d'un classeur en feuilles de calculs via VBA

   2.1. Qu’est-ce qu’une macro VBA ?

Comme nous venons de le voir, diviser un classeur en une multitudes de feuilles de calculs n’est pas très compliqué, mais cela peut demander pas mal de temps, en fonction du nombre de feuilles correspondant.

Pour gagner du temps, nous pouvons automatiser cette opération par la création d'une macro VBA.

Cette dernière permet en effet d'automatiser une suite d’instruction que nous pourrons ensuite répéter simplement en l’appelant, sans avoir à suivre manuellement toutes les étapes nécessaires.

De plus, la macro VBA peut être sauvegardée et réutilisée dans d'autres classeurs Excel, ce qui peut être utile si vous devez effectuer la même opération de division plusieurs fois.

Pour que cette macro soit facilement compréhensible par tous, je vous expliquerai chacune des lignes qui la compose, aucune notion de développement VBA n’est donc nécessaire pour suivre la suite de ce tutoriel.

   2.2. Comment créer une macro VBA ?

Dans Excel, les macros VBA sont saisies dans l'éditeur VBA.

Pour y accéder, nous pouvons utiliser plusieurs méthodes, la plus simple consistant à utiliser le raccourci clavier [Alt]+[F11].

Ce raccourci ouvre l'éditeur VBA, qui permet d'écrire, de modifier et de gérer les macros VBA dans Excel.

Une fois que nous sommes dans l'éditeur VBA, nous devons insérer un nouveau module dans le projet pour y insérer la macro VBA.

Un module est essentiellement un fichier contenant du code VBA qui permet d'effectuer une tâche spécifique.

Pour ajouter un module, nous nous rendons dans le menu "Insertion" en haut de la fenêtre de l'éditeur VBA, et nous cliquons sur "Module".

Cette action ajoutera un nouveau module vide au projet, nommé "Module 1" et accessible dans l'explorateur de projet, sur la gauche de la fenêtre de l'éditeur VBA.

C'est ici que nous écrirons le code de notre macro VBA pour diviser le classeur en feuilles de calculs.

2.3. Création de la macro diviserClasseur()

Ensuite, une fois que nous sommes dans le « Module 1 », nous pouvons créer la macro qui va nous permettre de procéder à la division d'un classeur en feuilles de calculs distinctes.

Pour créer une macro, nous utilisons le mot-clé « Sub », suivi du nom de la macro, ici "diviserClasseur".

Ensuite, nous validons en appuyant sur la touche [Entrée] du clavier pour que VBA valide la macro en insérant la ligne « End Sub ».

Maintenant, tout ce que nous allons saisir entre ces deux lignes sera exécuté automatiquement dès que nous appellerons la macro « diviserClasseur », comme nous le verrons un peu plus tard.

Dans cette macro, nous allons commencer par déclarer une variable « nomDossier » de type String qui contiendra le chemin d'accès au dossier dans lequel les feuilles seront enregistrées en utilisant la ligne :

Dim nomDossier As String
nomDossier = "C:\temp\divisionExcel\" &  ActiveWorkbook.Name & "\"

Ensuite, nous allons vérifier si le dossier nommé nomDossier existe ou pas.

Si ce n'est pas le cas, nous allons créer ce dossier en utilisant la commande « MkDir » :

If Dir(nomDossier) = "" Then
        MkDir nomDossier
    End If

Puis, nous allons mettre en place une boucle « For Each » qui va nous permettre de parcourir toutes les feuilles du classeur actif.

En programmation, une boucle est une notion qui permet de répéter un bloc d'instructions un certain nombre de fois prédéfini.

En VBA, il existe plusieurs types de boucles, dont la boucle « For Each » qui nous intéresse ici, qui est utilisée pour parcourir des collections d'objets, telles que des tableaux, des listes ou des feuilles de calcul dans Excel.

Elle permet ainsi de récupérer chaque élément de la collection les uns après les autres, en exécutant un bloc d'instructions pour chaque élément.

Pour mettre en place cette boucle, nous allons tout d’abord déclarer une variable nommée feuille de type Worksheet, qui sera utilisée pour stocker chaque feuille du classeur une par une :

    Dim feuille As Worksheet

Puis, nous initialisons la boucle « For Each » en indiquant que pour chaque feuille du classeur actif (déterminé par ActiveWorkbook), la variable « feuille » prendra successivement la valeur de chaque feuille, ce qui permet de les traiter une par une.

    For Each feuille In ActiveWorkbook.Sheets
    Next

Ainsi, nous allons pouvoir boucler sur toutes les feuilles du classeur.

Et pour commencer, nous allons enregistrer le nom du classeur que nous allons générer, en utilisant le nom de la feuille de calcul et en y ajoutant l’extension « .xlsx ».

Ce nom, nous allons le stocker dans la variable « nomFichier » en utilisant la ligne :

Dim nomFichier As String
nomFichier = feuille.Name & ".xlsx"

Ensuite, nous allons copier chaque feuille dans un nouveau classeur que nous enregistrerons juste après en utilisant ce nom « nomFichier », à l’intérieur du dossier « nomDossier » en utilisant la ligne :

feuille.Copy
ActiveWorkbook.SaveAs Filename:=nomDossier & nomFichier,  FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close False

Pour finir avec la macro, nous allons afficher un message à l’utilisateur pour lui indiquer que toutes les feuilles ont été exportées, et nous en profiterons pour lui demander s'il souhaite ouvrir le dossier contenant les fichiers exportés en utilisant la ligne :

If MsgBox("Toutes les feuilles ont été  exportées. Voulez-vous ouvrir le dossier contenant les fichiers exportés  ?", vbYesNo, "Ouvrir le dossier ?") = vbYes Then
        Shell "explorer.exe """ &  nomDossier & """", vbNormalFocus
    End If

Ici, nous ouvrons le dossier contenant les fichiers exportés par la macro en utilisant la fonction « Shell », qui permet d'exécuter une commande Windows depuis l’invite de commande.

La commande exécutée appelle le programme de Windows « Explorer.exe » qui permet d'afficher le contenu des dossiers.

Nous lui passons ensuite en argument le chemin du dossier à ouvrir, qui est stocké dans la variable « nomDossier », qui a été définie précédemment dans la macro.

Cette variable est entourée de guillemets doubles pour indiquer à Windows que c'est un chemin de fichier.

Le dernier argument, « vbNormalFocus », indique à Windows de placer la fenêtre de l'explorateur de fichiers en premier plan, afin qu'elle soit visible à l'utilisateur.

Maintenant que la macro est terminée, il suffit d’appuyer sur la touche [F5] pour l’exécuter.

Nous verrons alors Excel effectuer des opérations en manipulant les fenêtres, et pour finir, ce dernier va nous afficher un message pour nous demander s’il doit afficher le dossier.

   2.4. Avantages / Inconvénients de la méthode

Cette méthode est rapide et efficace pour diviser un classeur en feuilles de calculs en grande quantité.

Elle permet également de nommer les feuilles directement depuis la macro, ce qui peut être pratique pour gagner du temps.

Pour que les macros soient conservées dans le classeur, il est important de bien penser à l'enregistrer en tant que classeur Excel avec macros, c'est-à-dire avec l'extension « .xlsm ».

Si vous enregistrez le classeur en tant que classeur Excel standard (« .xlsx »), la macro sera supprimée et vous ne pourrez plus l’utiliser.

Pour pouvoir appeler rapidement la macro de division de classeur, nous pouvons également l'enregistrer dans le classeur des macros personnelles.