L’une des tâches les plus courantes des utilisateurs d’Excel consiste à transférer les données filtrées vers de nouvelles feuilles. Par exemple, il peut être nécessaire de transférer les ensembles de données avec des lignes séparées pour les régions de l’est, de l’ouest, du sud et du nord du pays sur des feuilles séparées créées pour chacune de ces zones. En règle générale, dans ce cas, vous devez filtrer les données pour chaque région, puis les copier et les coller dans de nouvelles feuilles. Cela peut être un défi lorsque vous devez le faire en même temps. Et si vous avez constamment à faire avec de telles tâches? Tout ce que vous avez à faire est de dire que c’est une tâche fastidieuse.
Voici la bonne nouvelle: vous pouvez utiliser des macros pour effectuer tout ce travail.
Comment ca marche?
Les contraintes de base pour cette macro sont, par essence, assez simples.
La macro traite ensuite les données dans ce champ, en récupérant les valeurs uniques (Pôinocny, Sud-Est, Est, Ouest). En outre, chacune de ces valeurs est utilisée séparément en tant que critère de filtrage pour la fonction de filtrage automatique des données en cours de traitement.
Chaque fois qu’une région est filtrée, la macro copie la plage de cellules ainsi obtenue et colle les données dans une nouvelle feuille. Après avoir été collés, le nom de la feuille est donné à partir du critère de filtrage.
Avec la première approche, la macro semble être compliquée, il est donc utile de prendre un peu de temps et d’examiner attentivement les détails de chacune des étapes suivantes.
Comment ca marche?
Les contraintes de base pour cette macro sont, par essence, assez simples.
La macro traite ensuite les données dans ce champ, en récupérant les valeurs uniques (Pôinocny, Sud-Est, Est, Ouest). En outre, chacune de ces valeurs est utilisée séparément en tant que critère de filtrage pour la fonction de filtrage automatique des données en cours de traitement.
Chaque fois qu’une région est filtrée, la macro copie la plage de cellules ainsi obtenue et colle les données dans une nouvelle feuille. Après avoir été collés, le nom de la feuille est donné à partir du critère de filtrage.
Avec la première approche, la macro semble être compliquée, il est donc utile de prendre un peu de temps et d’examiner attentivement les détails de chacune des étapes suivantes.
Sub CreerFeuilFilt() 'Étape 1: Déclaration des variables Dim MySheet As Worksheet Dim MyRange As Range Dim UList As Collection Dim UListValue As Variant Dim i As Long 'Étape 2: Configurez une feuille contenant Autofilter Set MySheet = ActiveSheet 'Étape 3: Si vous n'avez pas utilisé le filtre automatique dans la feuille de calcul, terminez If MySheet.AutoFilterMode = False Then Exit Sub End If 'Étape 4: Spécifiez les colonnes contenant les données à filtrer Set MyRange = Range(MySheet.AutoFilter.Range.Columns(1).Address) 'Étape 5: Créez un nouveau jeu Set UList = New Collection 'Étape 6: Terminez la collection avec des valeurs uniques On Error Resume Next For i = 2 To MyRange.Rows.Count UList.Add MyRange.Cells(i, 1), CStr(MyRange.Cells(i, 1)) Next i On Error GoTo 0 'Étape 7: Commencez la boucle pour la collecte de valeurs For Each UListValue In UList 'Étape 8: Supprimez les feuilles de calcul précédemment créées On Error Resume Next Application.DisplayAlerts = False Sheets(CStr(UListValue)).Delete Application.DisplayAlerts = True On Error GoTo 0 'Étape 9: Filtrer l'autofiltre pour ajuster la valeur actuelle MyRange.AutoFilter Field:=1, Criteria1:=UListValue 'Étape 10: Copiez les données filtrées dans la nouvelle feuille MySheet.AutoFilter.Range.Copy Worksheets.Add.Paste ActiveSheet.Name = Left(UListValue, 30) Cells.EntireColumn.AutoFit 'Étape 11: La boucle passe à la valeur suivante de la collection Next UListValue 'Étape 12: Retournez à la feuille principale et retirez les filtres MySheet.AutoFilter.ShowAllData MySheet.Select End Sub
1. Dans la première étape, nous commençons par définir cinq variables. MySheet est une variable utilisée pour identifier une feuille dans laquelle se trouvent des données provenant de l’autofiltre. MyRange est une variable qui stocke la plage du champ utilisé pour le filtrage.
(dans ce cas c’est un champ de région). UList est une collection que nous utiliserons pour obtenir des valeurs uniques à partir de la zone filtrée. UListValue stocke des valeurs uniques uniques obtenues lors du passage dans les cellules suivantes. Enfin, la variable de contrôle et la variable en cours d’exécution servent de compteur pour la variable MyRange.
2. À l’étape deux, la variable MySheet est définie pour stocker des données.
0 feuille dans laquelle la fonction de filtrage automatique a été utilisée C’est une étape importante, car dans la partie suivante de la macro, nous nous référerons à cette feuille. Dans ce cas, nous supposons que la macro sera exécutée dans la feuille de calcul dans laquelle le filtre automatique est utilisé. Nous définissons donc la variable sur ActiveSheet.
Au lieu de pointer sur une feuille de calcul active (ActiveSheet), vous pouvez également entrer le nom d’une feuille spécifique, par exemple: Définir MyBook = Sheets (“Nom de la feuille”). C’est une solution plus sûre car elle minimise le risque d’erreur
associé à l’exécution accidentelle de la macro dans la mauvaise feuille. Cela garantit également que la macro ne fonctionnera que sur une feuille bien définie.
3. Dans la troisième étape, vous vérifiez la propriété AutoFilterMode pour vous assurer que la fonctionnalité Filtre automatique est sur la feuille de calcul. Sinon, la procédure se termine.
4. Si la macro atteint la quatrième étape, vous pouvez être sûr que le filtre automatique est réellement utilisé dans la feuille de calcul.
Maintenant, il est nécessaire de récupérer le numéro de colonne contenant les valeurs qui seront utilisées ultérieurement pour effectuer l’analyse, sur la base desquelles les ensembles de données seront placés dans des feuilles séparées. Comme vous pouvez le voir à la figure 5.6, la colonne région est la première colonne de l’étendue de la fonction AutoPilot. Par conséquent, définissez la variable MyRange sur Columns (1) de la portée du filtre automatique. C’est important! La colonne indiquée servira à créer une liste de valeurs uniques nécessaires à l’analyse des données. En adaptant la macro à vos besoins, changez le numéro de colonne pour indiquer le champ nécessaire au traitement des données.
5. À la cinquième étape, un objet – la collection UList – est créé. Une collection est un conteneur pouvant stocker un tableau d’éléments uniques.
En fait, la collection ne peut stocker que des éléments uniques. Si nous essayons d’y insérer des données répétitives, cela provoquera l’apparition d’un bogue. Pour ces raisons, c’est un excellent moyen de rechercher
1 stockage de la liste des objets uniques.
Dans ce cas, la collection sera utilisée pour stocker une liste d’éléments uniques passés par la variable MyRange. Dans ce scénario, étant donné que la variable MyRange pointe vers la colonne région, la collection peut stocker des noms de région (est, ouest, nord, sud).
6. Dans l’étape intelligente, la collection UList est remplie avec des données uniques provenant de la variable MyRange.
Pour cela, une variable de contrôle est utilisée et une boucle est appelée pour les lignes de la colonne MyRange. La variable de contrôle et l’action de la deuxième ligne, car la première est l’en-tête de la table (région). Et le contenu de l’en-tête ne doit pas être inclus dans la liste des éléments uniques de la collection créée précédemment.
À chaque itération, la macro tente d’ajouter des données de la cellule actuelle à la collection UList. La syntaxe pour ajouter un élément à la collection est la suivante:
NameCollection.Add ItemName, Unique_identifier
Dans ce cas, le contenu de chaque cellule de la variable MyRange est utilisé comme nom de l’élément et son identificateur unique. Dans le cas d’un élément déjà existant dans la collection UList, une erreur sera renvoyée. Vous devez donc placer une ligne: On Error Resume Next et On Error Goto 0. Grâce à cela, nous pouvons être certains que lorsqu’un doublon apparaîtra, la collection ULi st l’ignorera. Une fois l’opération en boucle terminée, nous avons une liste d’éléments uniques à partir des données obtenues à partir de la variable MyRange. Rappelez-vous que dans ce scénario, cela signifie une liste de régions (est, ouest, midi et nord).
Étape 7 travaillons uniquement sur la collection UList. Il stocke une liste d’éléments qui seront utilisés à la fois comme critères de filtre (filtre automatique) et comme noms pour les feuilles nouvellement créées (feuilles). La macro commence à boucler la liste des éléments avec la variable UListValue.
8. Chaque fois qu’une macro est exécutée, une nouvelle feuille portant le nom de cet élément est créée pour chaque élément unique de la zone de filtre. Si la macro est exécutée plusieurs fois, un message d’erreur peut s’afficher, car une feuille de nom identique à celui qui existe déjà sera créée.
Afin d’empêcher ce développement, toutes les feuilles de calcul voisines dont les noms correspondent à ceux de la variable UListValue sont supprimées à l’étape suivante.
9. À l’étape neuf, la variable UListValue est utilisée pour le filtrage (filtre automatique). Les données stockées par la variable ULi stValue sont définies dynamiquement en tant que critères (Critères) pour le champ (Champ1):
MyRange.AutoFilter Champ: = 1, Critère1: = UListValue
Le numéro de champ est très important ici! Étant donné que le champ est le premier (voir la figure 5.6), il convient de le décrire comme suit: Champ = 1. Si vous utilisez une macro à vos propres fins, modifiez le numéro du champ (Champ) afin qu’il corresponde aux données en cours de traitement.
10. Chaque objet Filtre automatique possède la propriété Range. Il retourne des informations sur les lignes auxquelles l’autofiltration a été appliquée. Ce sont simultanément des lignes visibles dans la plage de données filtrée. À l’étape dix, la méthode Copier a été utilisée pour copier les lignes filtrées et les coller dans un nouveau classeur. La macro donne alors à la feuille le nom correspondant à ^
la valeur de la variable UListValue. La fonction Left a été utilisée pour entrer le nom de la feuille (UListValue). Grâce à cela, Excel limitera la longueur du nom de la feuille à 31 caractères de la chaîne téléchargée depuis UListValue. La limitation de la longueur du nom est imposée par Excel. La saisie d’un nom de plus de 31 caractères entraînera une erreur.
11. À l’étape onze, la boucle passe à la valeur suivante de la collection U
3. Dans la troisième étape, vous vérifiez la propriété AutoFilterMode pour vous assurer que la fonctionnalité Filtre automatique est sur la feuille de calcul. Sinon, la procédure se termine.
4. Si la macro atteint la quatrième étape, vous pouvez être sûr que le filtre automatique est réellement utilisé dans la feuille de calcul.
Maintenant, il est nécessaire de récupérer le numéro de colonne contenant les valeurs qui seront utilisées ultérieurement pour effectuer l’analyse, sur la base desquelles les ensembles de données seront placés dans des feuilles séparées. Comme vous pouvez le voir à la figure 5.6, la colonne région est la première colonne de l’étendue de la fonction AutoPilot. Par conséquent, définissez la variable MyRange sur Columns (1) de la portée du filtre automatique. C’est important! La colonne indiquée servira à créer une liste de valeurs uniques nécessaires à l’analyse des données. En adaptant la macro à vos besoins, changez le numéro de colonne pour indiquer le champ nécessaire au traitement des données.
5. À la cinquième étape, un objet – la collection UList – est créé. Une collection est un conteneur pouvant stocker un tableau d’éléments uniques.
En fait, la collection ne peut stocker que des éléments uniques. Si nous essayons d’y insérer des données répétitives, cela provoquera l’apparition d’un bogue. Pour ces raisons, c’est un excellent moyen de rechercher
1 stockage de la liste des objets uniques.
Dans ce cas, la collection sera utilisée pour stocker une liste d’éléments uniques passés par la variable MyRange. Dans ce scénario, étant donné que la variable MyRange pointe vers la colonne région, la collection peut stocker des noms de région (est, ouest, nord, sud).
6. Dans l’étape intelligente, la collection UList est remplie avec des données uniques provenant de la variable MyRange.
Pour cela, une variable de contrôle est utilisée et une boucle est appelée pour les lignes de la colonne MyRange. La variable de contrôle et l’action de la deuxième ligne, car la première est l’en-tête de la table (région). Et le contenu de l’en-tête ne doit pas être inclus dans la liste des éléments uniques de la collection créée précédemment.
À chaque itération, la macro tente d’ajouter des données de la cellule actuelle à la collection UList. La syntaxe pour ajouter un élément à la collection est la suivante:
NameCollection.Add ItemName, Unique_identifier
Dans ce cas, le contenu de chaque cellule de la variable MyRange est utilisé comme nom de l’élément et son identificateur unique. Dans le cas d’un élément déjà existant dans la collection UList, une erreur sera renvoyée. Vous devez donc placer une ligne: On Error Resume Next et On Error Goto 0. Grâce à cela, nous pouvons être certains que lorsqu’un doublon apparaîtra, la collection ULi st l’ignorera. Une fois l’opération en boucle terminée, nous avons une liste d’éléments uniques à partir des données obtenues à partir de la variable MyRange. Rappelez-vous que dans ce scénario, cela signifie une liste de régions (est, ouest, midi et nord).
Étape 7 travaillons uniquement sur la collection UList. Il stocke une liste d’éléments qui seront utilisés à la fois comme critères de filtre (filtre automatique) et comme noms pour les feuilles nouvellement créées (feuilles). La macro commence à boucler la liste des éléments avec la variable UListValue.
8. Chaque fois qu’une macro est exécutée, une nouvelle feuille portant le nom de cet élément est créée pour chaque élément unique de la zone de filtre. Si la macro est exécutée plusieurs fois, un message d’erreur peut s’afficher, car une feuille de nom identique à celui qui existe déjà sera créée.
Afin d’empêcher ce développement, toutes les feuilles de calcul voisines dont les noms correspondent à ceux de la variable UListValue sont supprimées à l’étape suivante.
9. À l’étape neuf, la variable UListValue est utilisée pour le filtrage (filtre automatique). Les données stockées par la variable ULi stValue sont définies dynamiquement en tant que critères (Critères) pour le champ (Champ1):
MyRange.AutoFilter Champ: = 1, Critère1: = UListValue
Le numéro de champ est très important ici! Étant donné que le champ est le premier (voir la figure 5.6), il convient de le décrire comme suit: Champ = 1. Si vous utilisez une macro à vos propres fins, modifiez le numéro du champ (Champ) afin qu’il corresponde aux données en cours de traitement.
10. Chaque objet Filtre automatique possède la propriété Range. Il retourne des informations sur les lignes auxquelles l’autofiltration a été appliquée. Ce sont simultanément des lignes visibles dans la plage de données filtrée. À l’étape dix, la méthode Copier a été utilisée pour copier les lignes filtrées et les coller dans un nouveau classeur. La macro donne alors à la feuille le nom correspondant à ^
la valeur de la variable UListValue. La fonction Left a été utilisée pour entrer le nom de la feuille (UListValue). Grâce à cela, Excel limitera la longueur du nom de la feuille à 31 caractères de la chaîne téléchargée depuis UListValue. La limitation de la longueur du nom est imposée par Excel. La saisie d’un nom de plus de 31 caractères entraînera une erreur.
11. À l’étape onze, la boucle passe à la valeur suivante de la collection U
Lettre.
12. La macro termine l’opération en basculant sur les données d’origine du filtre automatique et en supprimant simultanément les paramètres de tous les filtres.
Vous pourriez vous demander comment créer un nouveau classeur pour chaque élément obtenu à la suite de la fonction Filtre automatique. C’est une modification relativement facile à mettre en œuvre. Tout ce que vous avez à faire est de remplacer le code de l’étape 10 par les lignes suivantes:
‘Étape 10: Copiez les données filtrées dans le nouveau classeur ActiveSheet.AutoFilter.Range.Copy. Ajoutez des feuilles de travail (1) .Paste Cells.EntireColumn. AutoFit ActiveWorkbook.SaveAs _
Nom de fichier: = “C: \ Temp \” & CStr (UListValue) & “.xlsx”
ActiveWorkbook.Close
Assurez-vous de saisir le chemin de stockage du fichier dans le dossier. Si vous le souhaitez, vous pouvez le configurer en fonction de vos besoins.
Comment utiliser la macro?
Pour implémenter cette macro, vous pouvez copier et coller son code dans un module standard. À cette fin:
1. Utilisez les touches Alt + F11 pour activer Visual Basic Editor.
2. Dans la fenêtre Projet, cliquez avec le bouton droit sur le nom du projet ou de la feuille de calcul.
3. Sélectionnez Insérer / Module.
4. Tapez ou collez le code.
12. La macro termine l’opération en basculant sur les données d’origine du filtre automatique et en supprimant simultanément les paramètres de tous les filtres.
Vous pourriez vous demander comment créer un nouveau classeur pour chaque élément obtenu à la suite de la fonction Filtre automatique. C’est une modification relativement facile à mettre en œuvre. Tout ce que vous avez à faire est de remplacer le code de l’étape 10 par les lignes suivantes:
‘Étape 10: Copiez les données filtrées dans le nouveau classeur ActiveSheet.AutoFilter.Range.Copy. Ajoutez des feuilles de travail (1) .Paste Cells.EntireColumn. AutoFit ActiveWorkbook.SaveAs _
Nom de fichier: = “C: \ Temp \” & CStr (UListValue) & “.xlsx”
ActiveWorkbook.Close
Assurez-vous de saisir le chemin de stockage du fichier dans le dossier. Si vous le souhaitez, vous pouvez le configurer en fonction de vos besoins.
Comment utiliser la macro?
Pour implémenter cette macro, vous pouvez copier et coller son code dans un module standard. À cette fin:
1. Utilisez les touches Alt + F11 pour activer Visual Basic Editor.
2. Dans la fenêtre Projet, cliquez avec le bouton droit sur le nom du projet ou de la feuille de calcul.
3. Sélectionnez Insérer / Module.
4. Tapez ou collez le code.