Lorsque vous travaillez avec des sources de données variées, vous pouvez souvent avoir du mal à compiler plusieurs classeurs et feuilles de calcul avant d'arriver à un élément de données final. Imaginez une situation où vous avez quelques centaines de classeurs à combiner avant même de pouvoir commencer votre journée.
Personne ne veut passer des heures interminables à travailler sur différentes sources, à ouvrir chaque classeur, à copier et coller les données de différentes feuilles, avant de finalement créer un classeur consolidé. Et si une macro VBA pouvait le faire pour vous ?
Avec ce guide, vous pouvez créer votre propre code de macro Excel VBA pour consolider plusieurs classeurs, le tout en quelques minutes si les fichiers de données sont nombreux.
Pré-requis pour créer votre propre code de macro VBA
Vous avez besoin d'un classeur pour héberger le code VBA, tandis que le reste des classeurs de données source sont séparés. De plus, créez un classeur consolidé pour stocker les données consolidées de tous vos classeurs.
Créer un dossier Consolidation à votre emplacement préféré pour stocker tous vos classeurs sources. Lorsque la macro s'exécute, elle bascule entre chaque classeur stocké dans ce dossier, copie le contenu de différentes feuilles et le place dans le classeur consolidé.
Création de votre propre code Excel VBA
Une fois que les pré-requis sont réglés, il est temps de se plonger dans le code et de commencer à pirater les bases pour l'adapter à vos besoins.
Appuyez sur Alt+F11 touche sur Excel pour ouvrir l'éditeur de code de macro VBA. Collez le code écrit ci-dessous et enregistrez le fichier en tant que classeur compatible avec les macros .xlsm extension.
sous openfiles
'déclarer les variables utilisées dans le code VBA
Dim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Long
'désactiver ces fonctions pour améliorer le traitement du code
Avec Application
.DisplayAlerts = False
.ScreenUpdating = False
Terminer par
'changer le chemin du dossier où vos fichiers vont être enregistrés
MyFolder = InputBox"Entrez le chemin du dossier de consolidation" & "\"
'définir la référence du dossier dans une variable macro
MonFichier = DirMonDossier
'ouvrir une boucle pour parcourir chaque classeur individuel stocké dans le dossier
Do While LenMyFile > 0
'activer le classeur de consolidation
Windows"Consolidation".Activer
'calculer la dernière ligne remplie
Plage"a1048576".Sélectionner
Selection.EndxlUp.Select
ActiveCell.Offset1, 0.Sélectionner
'ouvrir le premier classeur dans le dossier Consolidation
Workbooks.Open Filename :=MyFolder & MyFile
WindowsMonFichier.Activer
'passer en revue chaque feuille dans les classeurs pour copier les données
Dim ws As Worksheet
Pour chaque ws en feuilles
ws.Activer
ws.AutoFilterMode = False
'ignorer l'en-tête et copier les données de la ligne 2
Si Cells2, 1 = "" Alors GoTo 1
Aller à 10
1 : Suivant
10 : Plage"a2:az20000".Copier
Windows"Consolidation".Activer
'coller le contenu copié
ActiveFeuille.Coller
WindowsMonFichier.Activer
'ferme le classeur ouvert une fois les données collées
ActiveWorkbook.Fermer
'vider le cache pour stocker la valeur du prochain classeur
MonFichier = Dir
'ouvre le fichier suivant dans le dossier
boucle
'activer les fonctions désactivées pour une utilisation future
Avec Application
.DisplayAlerts = True
.ScreenUpdating = True
Terminer par
Fin du sous-titreLe code VBA expliqué
La première partie du code définit un sous-programme, qui contient tout votre code VBA. Définissez le sous-programme avec sous, suivi du nom du code. Le sous-nom peut être n'importe quoi ; idéalement, vous devriez conserver un nom correspondant au code que vous êtes sur le point d'écrire.
Excel VBA comprend les variables créées par l'utilisateur et leurs types de données correspondants déclarés avec faible dimension.
Pour améliorer la vitesse de traitement de votre code, vous pouvez désactiver la mise à jour de l'écran et supprimer toutes les alertes, car cela ralentit l'exécution du code.
L'utilisateur sera invité à indiquer le chemin du dossier dans lequel les fichiers de données sont stockés. Une boucle est créée pour ouvrir chaque classeur stocké dans le dossier, copier les données de chaque feuille et les ajouter au Consolidation classeur.
Le classeur de consolidation est activé pour qu'Excel VBA puisse calculer la dernière ligne remplie. La dernière cellule de la feuille de calcul est sélectionnée et la dernière ligne est calculée dans le classeur à l'aide de la fonction de décalage. Ceci est très utile lorsque la macro démarreen ajoutant des données à partir des fichiers source.
Lorsque la boucle ouvre le premier fichier source, les filtres sont supprimés de chaque feuille s'ils existent et les données allant de A2 à AZ20000 seront copiées et collées dans le classeur de consolidation.
Le processus est répété jusqu'à ce que toutes les feuilles de classeur soient ajoutées dans le classeur principal.
Enfin, le fichier source est fermé une fois toutes les données collées. Le classeur suivant est ouvert afin que la macro VBA puisse répéter les mêmes étapes pour le prochain ensemble de fichiers.
La boucle est codée pour s'exécuter jusqu'à ce que tous les fichiers soient automatiquement mis à jour dans le classeur principal.
personnalisations basées sur l'utilisateur
Parfois, vous ne voulez pas vous soucier des invites intégrées, surtout si vous êtes l'utilisateur final. Si vous préférez coder en dur le chemin du dossier Consolidation dans le code, vous pouvez modifier cette partie du code :
MyFolder = InputBox"Entrez le chemin du dossier de consolidation" & "\"À :
MonDossier = "Chemin du dossier" & "\"En outre, vous pouvez également modifier les références de colonne, car l'étape n'est pas incluse dans ce code. Remplacez simplement la référence de fin de colonne par la dernière valeur de colonne remplie AZ, dans ce cas. Vous devez vous rappeler que la dernière valeur rempliela ligne est calculée via le code macro, vous devez donc uniquement modifier la référence de la colonne.
Pour tirer le meilleur parti de cette macro, vous ne pouvez l'utiliser que pour consolider des classeurs dans le même format. Si les structures sont différentes, vous ne pouvez pas utiliser cette macro VBA.
Consolidation de plusieurs classeurs à l'aide de la macro Excel VBA
Créer et modifier un code Excel VBA est relativement facile, surtout si vous comprenez certaines des nuances du code. VBA parcourt systématiquement chaque ligne de code et l'exécute ligne par ligne.
Si vous apportez des modifications au code, vous devez vous assurer de ne pas modifier l'ordre des codes, car cela perturberait l'exécution du code.
.jpg?q=50&fit=crop&w=330&h=170&dpr=1.5)
