Regarder la vidéo - Comment diviser chaque feuille Excel en fichiers séparés
Si vous avez un classeur Excel avec de nombreuses feuilles de calcul, il n'y a pas de moyen simple de diviser chacune de ces feuilles en fichiers Excel séparés et de les enregistrer séparément.
Cela peut être nécessaire lorsque vous avez des feuilles pour différents mois ou régions ou produits/clients et que vous souhaitez obtenir rapidement un classeur séparé pour chaque feuille (sous forme de fichier Excel ou de PDF).
Bien qu'il existe un moyen manuel de diviser les feuilles en classeurs séparés, puis de les enregistrer, il est inefficace et sujet aux erreurs.
Dans ce didacticiel, je vais vous donner un code VBA simple que vous pouvez utiliser pour diviser rapidement (en quelques secondes) toutes les feuilles de calcul dans leurs propres fichiers séparés, puis les enregistrer dans un dossier spécifié.
Diviser chaque feuille de calcul dans un fichier Excel distinct
Supposons que vous ayez un classeur comme indiqué ci-dessous où vous avez une feuille de calcul pour chaque mois.
Pour diviser ces feuilles dans un fichier Excel séparé, vous pouvez utiliser le code VBA ci-dessous :
'Code créé par Sumit Bansal de trumpexcel.com Sub SplitEachWorksheet() Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False pour chaque ws dans ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Nom de fichier : = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Vous devez vous assurer de certaines choses avant d'utiliser le code VBA ci-dessus :
- Créez un dossier dans lequel vous souhaitez obtenir tous les fichiers résultants.
- Enregistrez le fichier Excel principal (qui contient toutes les feuilles de calcul que vous souhaitez sous forme de fichiers séparés) dans ce dossier.
Une fois cela fait, vous pouvez mettre le code VBA ci-dessus dans le fichier et exécuter le code.
Le code ci-dessus est écrit de manière à récupérer l'emplacement du dossier en utilisant le chemin du fichier (dans lequel le code est exécuté). C'est pourquoi il est important d'enregistrer d'abord le fichier dans le dossier, puis d'utiliser ce code.
Comment fonctionne le code VBA - Le code ci-dessus utilise une simple boucle For Next qui parcourt chaque feuille de calcul, crée une copie de la feuille de calcul dans un classeur Excel, puis enregistre ce classeur Excel dans le dossier spécifié (qui est le même que celui contenant le fichier principal avec tous les des draps).
Vous trouverez ci-dessous les étapes pour placer ce code VBA dans le classeur Excel (ce seront les mêmes pour toutes les autres méthodes présentées dans ce didacticiel):
Où mettre ce code ?
Vous trouverez ci-dessous les étapes pour placer le code dans Visual Basic Editor où il peut être exécuté :
- Cliquez sur l'onglet Développeur.
- Dans le groupe Code, cliquez sur l'option Visual Basic. Cela ouvrira l'éditeur VB. [Vous pouvez également utiliser le raccourci clavier - ALT + F11]
- Dans l'éditeur VB, cliquez avec le bouton droit sur l'un des objets du classeur sur lequel vous travaillez.
- Passez le curseur sur l'option Insérer
- Cliquez sur Module. Cela va insérer un nouveau module
- Double-cliquez sur l'objet Module. cela ouvrira la fenêtre de code pour le module
- Copiez le code VBA fourni ci-dessus et collez-le dans la fenêtre de code du module.
- Sélectionnez n'importe quelle ligne du code et cliquez sur le bouton de lecture vert dans la barre d'outils pour exécuter le code de macro VBA.
Les étapes ci-dessus diviseraient instantanément les feuilles de calcul en fichiers Excel séparés et les enregistreraient. Cela ne prend qu'une seconde si vous avez moins de feuilles de calcul. Si vous en avez beaucoup, cela peut prendre un certain temps.
Le nom de chaque fichier enregistré est le même que celui du nom de feuille qu'il avait dans le fichier principal.
Puisque vous avez placé un code VBA dans le classeur Excel, vous devez l'enregistrer au format .XLSM (qui est le format prenant en charge les macros). Cela garantira que la macro est enregistrée et fonctionne lorsque vous ouvrez ce fichier ensuite.
Notez que j'ai utilisé les lignes Application.ScreenUpdating = Faux et Application.DisplayAlerts = Faux dans le code afin que tout se passe dans le backend et que rien ne se passe sur votre écran. Une fois que le code s'exécute et divise les feuilles et les enregistre, nous les remettons à TRUE.
Comme meilleure pratique, il est recommandé de créer une copie de sauvegarde du fichier principal (qui contient les feuilles que vous souhaitez diviser). Cela garantira que vous ne perdez pas vos données en cas de problème ou si Excel décide de ralentir ou de planter.
Divisez chaque feuille de calcul et enregistrez-les en tant que fichiers PDF distincts
Si vous souhaitez diviser les feuilles de calcul et les enregistrer sous forme de fichiers PDF au lieu de fichiers Excel, vous pouvez utiliser le code ci-dessous :
'Code créé par Sumit Bansal de trumpexcel.com Sub SplitEachWorksheet() Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False pour chaque ws dans ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.ExportatAsFi Type :=xlTypePDF, Nom de fichier :=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Choses à vérifier avant d'utiliser ce code :
- Créez un dossier dans lequel vous souhaitez obtenir tous les fichiers résultants.
- Enregistrez le fichier Excel principal (qui contient toutes les feuilles de calcul que vous souhaitez sous forme de fichiers séparés) dans ce dossier.
Le code ci-dessus divise chaque feuille du fichier Excel et l'enregistre au format PDF dans le même dossier que celui où vous avez enregistré le fichier Excel principal.
Diviser uniquement les feuilles de calcul contenant un mot/une phrase en fichiers Excel séparés
Si vous avez beaucoup de feuilles dans un classeur et que vous ne souhaitez diviser que les feuilles contenant un texte spécifique, vous pouvez également le faire.
Par exemple, supposons que vous ayez un fichier Excel dans lequel vous avez des données pour plusieurs années et que chaque feuille du fichier a le numéro de l'année comme préfixe. Quelque chose comme indiqué ci-dessous :
Maintenant, disons que vous voulez diviser toutes les feuilles pour 2021-2022 et les enregistrer en tant que fichiers Excel séparés. Pour ce faire, vous devez en quelque sorte vérifier le nom de chaque feuille de calcul et seules les feuilles portant le numéro 2021-2022 doivent être divisées et enregistrées, et le reste ne doit pas être modifié.
Cela peut être fait à l'aide du code de macro VBA suivant :
'Code créé par Sumit Bansal de trumpexcel.com Sub SplitEachWorksheet() Dim FPath As String Dim TexttoFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False pour chaque ws dans ThisWorkbook. Sheets If InStr(1, ws.Name, TexttoFind, vbBinaryCompare) 0 Then ws.Copy Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Dans le code ci-dessus, j'ai utilisé une variable TexttoFind, qui a été affectée à "2020" au début.
Le code VBA utilise ensuite la boucle For Next dans VBA pour parcourir chaque feuille de calcul, puis vérifier le nom de chaque fonction INSTR de la feuille de calcul. Cette fonction vérifie si le nom de la feuille de calcul contient ou non le mot 2021-2022. Si c'est le cas, il renverra un numéro de position où il trouve ce texte (qui est 2021-2022 dans ce cas).
Et s'il ne trouve pas le texte que nous recherchons, il renvoie 0.
Ceci est utilisé avec la condition IF Then. Ainsi, si un nom de feuille contient la chaîne de texte 2021-2022, il sera divisé et enregistré dans un fichier séparé. Et s'il n'a pas cette chaîne de texte, la condition IF ne serait pas remplie et rien ne se passerait.
