Vous avez un classeur avec de nombreuses feuilles de calcul. Vous souhaitez envoyer chaque feuille de calcul à une personne différente. Aujourd'hui, une macro pour séparer ces données.
Regarder la vidéo
- Joe + Others cherche un moyen d'enregistrer chaque feuille de calcul dans un fichier différent
- Utile pour Power Query ou après avoir utilisé Afficher les pages de filtre de rapport
Transcription vidéo
Apprendre Excel à partir du podcast, épisode 2107 - Divisez chaque feuille de calcul en un nouveau classeur
Salut, bon retour. Je suis et netcast. Je suis Bill Jelen.
Je savais au fond de ma tête que je devais le faire depuis longtemps, mais deux podcasts récents l'ont vraiment amené à la maison.
Tout récemment dans l'épisode 2106, où nous créions un PDF à partir de toutes les combinaisons de slicer. À la fin de cet épisode, j'ai montré une autre méthode dans laquelle nous créons de nombreux rapports pivot, mais cela les met tous sur le même classeur et j'ai reçu un e-mail de Joe en Californie qui dit bien, regardez, je dois envoyer chaque feuille de travail à un client différent, et même chose, dans mes séminaires Power Excel en direct où je montre cette astuce, les gens disent, eh bien non, nous ne voulons pas tout dans le même classeur, nous le voulons séparément et alors probablement encore plus important que cela, est de retour dans épisode 2077, où j'ai parlé de la façon dont Power Query a maintenant la capacité de combiner tous les fichiers Excel dans un dossier, non? Et c'est miraculeux. Cela fonctionne très bien. Si vous aviez 400 fichiers Excel, chacun avec une seule feuille de calcul, il récupérera toutes ces données de toutes ces feuilles de calcul et les placera dans une grille.Ce qui est génial, mais si nous avions presque le même problème. Un classeur avec 400 feuilles de calcul? Il ne peut pas le faire, non. Il ne peut pas gérer cela - pour le moment. Bon, donc pour le moment, le 1er juillet 2017, ça ne peut pas faire face à ça. Peut-être que dans six mois, il pourra gérer cela, mais pour le moment, il doit s'agir de classeurs à feuille unique.
Nous avons donc besoin d'un moyen de diviser les choses en fichiers individuels. D'accord, nous allons donc simplement configurer cela. Nous avons le classeur que j'ai fait en 2106 où nous avons les données, puis le tableau croisé dynamique d'origine et nous allons dans Analyser, Options, Afficher les pages de filtre de rapport et afficher les pages de la clé, et cela crée tout un tas de feuilles de calcul différentes pour moi et Je veux prendre ces feuilles de calcul et créer chacune est un fichier séparé, mais même si nous l'avons, il y a des choses comme Sheet2 et Data que je ne veux pas diviser.
Bien? Et bien sûr, pour chaque personne, ces trucs, ces, cette liste de feuilles de travail, que nous ne voulons pas diviser, va être différent, mais je vais supposer que presque tout le monde a des feuilles de travail qu'ils n'ont pas. ne veux pas se séparer.
Très bien, voici donc l'utilitaire que vous allez pouvoir télécharger. The Worksheets Splitter et ici j'ai une section dans la colonne B et c'est vraiment la seule chose dans la colonne B où vous pouvez lister ces feuilles de travail que vous ne voulez pas diviser. Cela peut être plus de deux. Vous pouvez en remplir autant que vous le souhaitez. Vous pouvez insérer de nouvelles lignes et ma manière bon marché, je ne voulais pas avoir à les parcourir en boucle dans la macro, si loin de votre vue ici, j'ai un endroit où la macro peut écrire le nom de la feuille de calcul actuelle, puis un simple petit VLOOKUP. Il dit, allez chercher cette feuille de travail sur laquelle nous travaillons en ce moment, voyez si elle est terminée dans la colonne B et et si c'est le cas, nous saurons que c'est celle que nous ne voulons pas exporter.
Très bien et encore une fois juste pour rendre cela aussi générique que possible, j'ai plusieurs plages nommées ici, mon chemin, mon préfixe, mon suffixe, mon type et ma pâte. D'accord, alors vous déterminez où vous voulez que ce truc aille. c: Rapports . Je veux que chaque fichier ait le nom de la feuille, mais avant le nom de la feuille, je vais mettre le préfixe de WB, Suffixe de fichier et rien et vous avez le choix ici: PDF ou XLSX.
Nous allons donc commencer par le XLSX, nous parlerons de ces valeurs de collage avant de sauvegarder plus tard. D'accord et en ce moment c'est la version du 1er juillet 2017, la première. Si nous améliorons cela, je vais simplement le remplacer sur la page Web et vous pouvez trouver la page Web là-bas dans les descriptions YouTube. Très bien, voici comment cela va fonctionner. C'est un fichier XLSM. Vous devez donc vous assurer que les macros sont autorisées. alt = "" T, M, S, pour la sécurité, vous devez être au moins à ce niveau ou en dessous. Bon si vous êtes en haut, vous devez changer, fermer le classeur, rouvrir. Lorsque vous ouvrez le classeur, il va dire, hey êtes-vous prêt à accepter les macros ici et ce n'est pas du tout une grosse macro: soixante-huit lignes de code et une grande partie de cela consiste simplement à retirer les valeurs du menu Feuille,quelles sont les variables maintenant.
La chose importante ici est que cela fonctionnera sur l'ActiveWorkbook. Vous allez donc basculer vers le classeur qui contient les données, puis appuyez sur CTRL SHIFT S pour l'exécuter et il détectera l'ActiveWorkbook et ce sera celui qu'il divise. Il attrape ("MyPath") et c'est juste parce que j'oublie toujours de mettre cette barre oblique inverse, si le dernier caractère n'est pas une barre oblique inverse, alors je vais ajouter une barre oblique inverse, puis voici le travail réel.
Pour chaque feuille de travail, dans l'original, dans les feuilles de travail WBO.Worksheets actives, nous allons tester pour voir si c'est une qui est là-bas et la colonne B. Si c'est, si ce n'est pas le cas, nous allons exporter cette feuille et je J'adore cette ligne de code. WS.copy dit, quand je prends ce classeur, cette feuille de travail, de ce gros classeur avec, vous connaissez 20 ou 400 feuilles de travail et nous allons à WS.copy, qui en fait une copie et la déplace vers un nouveau classeur et nous savons, nous savons que ce nouveau classeur deviendra désormais le classeur actif dans la macro et bien sûr il n'y a qu'une seule feuille dans ce classeur et cette feuille est la feuille active.
Donc, ici, je peux comprendre le nom du classeur. Définissez-le, Appliquer à cette variable d'objet, Nouveau classeur, Nouvelle feuille de calcul, puis plus tard, lorsque je dois fermer, je peux faire WBN.close après avoir fait le travail. Nous trouvons le nouveau nom de fichier en utilisant toutes les variables. Tuez ce fichier, s'il existe déjà, puis, s'il s'agit d'un fichier Excel, nous effectuons un enregistrement sous, s'il s'agit d'un PDF.
Et au fait, ce code PDF ne fonctionne que sous Windows, si vous êtes sur un Mac, désolé, vous allez devoir aller ailleurs pour trouver le code Mac équivalent. Je n'ai pas de Mac. Je sais qu'il existe un moyen d'enregistrer un PDF sur un Mac. Je sais que le code est différent. Vous devrez comprendre celui-là ou revenir au vrai Excel dans Windows et puis nous avons terminé, nous fermons.
D'accord, c'est donc juste une simple petite macro comme ça, passez à notre classeur de données ici, celui qui contient toutes les feuilles de calcul. Il y a 20 feuilles de travail différentes ici, plus les deux que je ne veux pas faire, puis CTRL SHIFT S comme ça et nous la regarderons clignoter pendant qu'elle crée chacune. Nous y sommes: 21 fichiers créés.
Allons jeter un œil dans l'Explorateur Windows et voici mes rapports OS (C :), créés pour chaque feuille de calcul, nommés dans le classeur d'origine, ils ont créé une nouvelle version avec WB à l'avant. Très bien maintenant, Joe, quand Joe m'a envoyé cette note, il a dit qu'il allait envoyer ces données aux clients et j'ai un peu paniqué au début parce que j'ai dit, attendez une seconde Joe, nous allons avoir un problème parce que vous êtes va envoyer Gary, ses données, non? Mais c'est, ah, vous connaissez un jeu de données en direct, un tableau croisé dynamique en direct. Tout ce qu'il y a ici, vous pourrez peut-être obtenir toutes les informations pour d'autres clients comme ça, non? Boy, vous ne voulez pas envoyer à un client A les informations pour tous les autres clients. Cela pourrait être un problème et en fait, quand j'ai relu la note des émissions, il était plus intelligent que moi, car il a dit:Je souhaite les créer sous forme de fichiers PDF. J'étais comme, d'accord, eh bien oui, nous n'avons pas à nous soucier des fichiers PDF, c'est bien, mais ce que j'ai ajouté ici, à la macro, c'était la possibilité de dire Coller les valeurs avant d'enregistrer? VRAI.
Donc, vous définissez cela égal à TRUE et cela va invoquer ce petit bout de code ici, où nous disons, If PasteV Then the UsedRange.Copy and then UsedRange.PasteSpecial (xlPasteValues), UsedRange, plutôt que de copier et coller les 17 milliards de cellules , ça va le limiter à bien, le UsedRange.
Bon, alors revenons en arrière, changez la feuille de calcul qui contient les données, CTRL SHIFT S pour la division, puis cette nouvelle version dans le répertoire des rapports, vous verrez qu'elle s'est débarrassée du tableau croisé dynamique et n'y a laissé que les données. Ainsi, ils ne peuvent pas accéder à toutes les données.
Très bien, nous allons essayer l'autre fonctionnalité. Nous allons essayer si nous passons d'Excel à PDF, changeons le préfixe en PDFFileOf, tout ce que nous voulons. Je n'essaierai même pas le suffixe, quelque chose. D'accord, puis passez aux données, CTRL SHIFT S. D'accord, donc nous obtenons les mêmes fichiers PDFFileOf le nom de la feuille de travail, quelque chose de PDF et nous devrions avoir juste de jolis petits PDF, comme ça.
Très bien, voilà le séparateur de feuilles de travail. Espérons que suffisamment générique, pour tout ce dont vous avez besoin. Téléchargez-le à nouveau à partir du lien dans les commentaires YouTube. Pour en savoir plus sur VBA, consultez ce livre Excel 2016 VBA et macros par moi-même et Tracy? 08: 50.640. Cliquez sur ce bouton dans le coin supérieur droit pour en savoir plus sur le livre.
Joe, de Californie, et beaucoup d'autres ont demandé un moyen d'enregistrer chaque feuille de calcul dans un fichier différent, sous forme de fichier PDF dans le cas de Joe ou de fichier Excel au cas où vous utiliseriez Power Query pour combiner des fichiers. J'ai donc créé un joli petit utilitaire gratuit générique. Vous pouvez télécharger et essayer.
Je tiens à remercier Joe d'avoir posé cette question et je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2107.xlsm
