Je hebt een werkboek met veel werkbladen. U wilt elk werkblad naar een andere persoon sturen. Vandaag een macro om die gegevens op te splitsen.
Bekijk video
- Joe + Others zoekt een manier om elk werkblad in een ander bestand op te slaan
- Handig voor Power Query of na gebruik van Show Report Filter Pages
Videotranscriptie
Leer Excel van Podcast, aflevering 2107 - Splits elk werkblad op in een nieuwe werkmap
Hallo, welkom terug. Ik ben en netcast. Ik ben Bill Jelen.
Ik wist in mijn achterhoofd dat ik dit al heel lang moest doen, maar twee recente podcasts brachten het echt, brachten het naar huis.
Onlangs in aflevering 2106, waar we een pdf aan het maken waren van All Slicer Combinations. Laat in die aflevering liet ik een alternatieve methode zien waarbij we veel spilrapporten maken, maar ze allemaal in hetzelfde werkboek plaatsen en ik kreeg een e-mail van Joe in Californië die zegt: kijk, ik moet elk werkblad naar een andere klant sturen, en hetzelfde, in mijn live Power Excel-seminars waar ik die truc laat zien, zeggen mensen: nou nee, we willen het niet allemaal in hetzelfde werkboek, we willen het apart en dan waarschijnlijk nog belangrijker dan dat, is terug in aflevering 2077, waar ik vertelde hoe Power Query nu de mogelijkheid heeft om alle Excel-bestanden in een map te combineren, toch? En dit is wonderbaarlijk. Het werkt geweldig. Als u 400 Excel-bestanden had, elk met één werkblad, haalt het al die gegevens uit al die werkbladen en plaatst deze in één raster.Dat is geweldig, maar als we bijna hetzelfde probleem hadden. Eén werkboek met 400 werkbladen? Het kan het toch niet doen. Daar kan het niet mee omgaan - nog niet. Juist, dus op dit moment, 1 juli 2017, kan het daar niet mee omgaan. Misschien kan het daar binnen zes maanden mee omgaan, maar op dit moment moeten het werkboeken met één vel zijn.
We hebben dus een manier nodig om dingen op te splitsen in individuele bestanden. Oké, dus laten we dit gewoon opzetten. We hebben het werkboek dat ik in 2106 heb gemaakt, waar we de gegevens hebben en vervolgens de originele draaitabel en we gaan naar Analyseren, Opties, Rapportfilterpagina's weergeven en pagina's van de sleutel tonen, en het creëert een hele reeks verschillende werkbladen voor mij en Ik wil die werkbladen nemen en maken dat ze allemaal een apart bestand zijn, maar hoewel we dat hebben, zijn er dingen zoals Sheet2 en Data die ik niet wil splitsen.
Oké? En natuurlijk zal voor elke persoon dat spul, die lijst met werkbladen, die we niet willen splitsen, anders zijn, maar ik denk dat bijna iedereen een aantal werkbladen heeft die ze niet hebben ' ik wil niet splitsen.
Oké, dus hier is het hulpprogramma dat u kunt downloaden. De Worksheet Splitter en hier heb ik een sectie in kolom B en het is echt het enige in kolom B waar je die Worksheets kunt vermelden die je niet wilt splitsen. Het kunnen er meer dan twee zijn. U kunt hier zoveel invullen als u wilt. Je kunt nieuwe rijen invoegen en op mijn goedkope manier, ik wilde deze niet doorlopen in de macro, zo ver buiten je zicht hier, heb ik een plek waar de macro de huidige werkbladnaam kan schrijven en dan een eenvoudige kleine VERT.ZOEKEN. Er staat: ga op zoek naar dit werkblad waar we nu aan werken, kijk of het voorbij is in kolom B en als dat zo is, dan weten we dat dat er een is die we niet willen exporteren.
Oké en dan weer om dit zo algemeen mogelijk te maken, ik heb hier verschillende benoemde bereiken, mijn pad, mijn voorvoegsel, mijn achtervoegsel, mijn type en mijn plak. Oké, dus zoek uit waar je dit spul naartoe wilt. c: Rapporten . Ik wil dat elk bestand de bladnaam heeft, maar vóór de bladnaam zet ik het voorvoegsel van WB, File Suffix en niets en dan heb je hier een keuze: PDF of XLSX.
Dus we beginnen met de XLSX, we zullen het hebben over deze plakwaarden voordat we later opslaan. Oké en op dit moment is dit versie 1 juli 2017, de eerste. Als we dit verbeteren, vervang ik het gewoon op de webpagina en kun je de webpagina daar beneden vinden in de YouTube-beschrijvingen. Oké, dus hier is hoe dit gaat werken. Het is een XLSM-bestand. Je moet er dus voor zorgen dat macro's zijn toegestaan. alt = "" T, M, S, voor veiligheid moet je minimaal op dit niveau of lager zijn. Precies als je bovenaan staat, moet je veranderen, het werkboek sluiten, opnieuw openen. Wanneer je het werkboek opent, zal het zeggen, hey, ben je bereid om de macro's hier te accepteren en het is helemaal geen grote macro: achtenzestig regels code en veel daarvan heeft te maken met het halen van de waarden uit het menu Vel,wat zijn nu de variabelen.
Het belangrijkste hier is echter dat het zal werken op het ActiveWorkbook. Dus je gaat naar het werkboek met de gegevens en dan op CTRL SHIFT S drukken om het uit te voeren en het zal het ActiveWorkbook detecteren en dat wordt het uitgesplitst. Het pakt ("MyPath") en het is alleen omdat ik altijd vergeet om die backslash te plaatsen, als het laatste karakter geen backslash is, dan ga ik een backslash toevoegen en dan hier beneden is dit het eigenlijke werk.
Voor elk werkblad, in het origineel, in de actieve WBO.Worksheets, gaan we testen of het er een is die daar staat en kolom B. Als dat zo is, als dat niet het geval is, gaan we dit blad exporteren en ik hou van deze regel code. WS.copy zegt dat wanneer ik dit werkboek, dit werkblad, uit dit grote werkboek neem, je 20 of 400 werkbladen kent en we gaan naar WS.copy, dat er een kopie van maakt en het naar een nieuw werkboek verplaatst. en we weten het, we weten dat die nieuwe werkmap nu het actieve werkboek in de macro wordt en er is natuurlijk maar één blad in die werkmap en dat blad is het actieve blad.
Dus hier kan ik de naam van het werkboek achterhalen. Stel het in, Toepassen op deze objectvariabele, Werkmap nieuw, Werkblad nieuw en later, als ik moet sluiten, kan ik WBN.close doen nadat ik het werk heb gedaan. We berekenen de nieuwe bestandsnaam met behulp van alle variabelen. Dood dat bestand, als het al bestaat, en als het een Excel-bestand is, slaan we het op alsof het een pdf is.
En trouwens, deze pdf-code werkt alleen in Windows, als je een Mac gebruikt, sorry, je zult ergens anders heen moeten om de equivalente Mac-code te achterhalen. Ik heb geen Mac. Ik weet dat er een manier is om een pdf op een Mac op te slaan. Ik weet dat de code anders is. Je zult dat moeten uitzoeken of terug moeten gaan naar het echte Excel in Windows en dan zijn we klaar, we sluiten.
Oké, dus het is maar een simpele kleine macro zoals deze, schakel hier over naar ons gegevenswerkboek, het werkboek met alle werkbladen. Er zijn hier 20 verschillende werkbladen, plus de twee die ik niet wil doen en dan CTRL SHIFT S op deze manier en we zullen zien hoe het knippert terwijl het elk maakt. Daar zijn we: 21 bestanden gemaakt.
Laten we een kijkje nemen in Windows Verkenner en hier zijn mijn OS (C :) -rapporten, het is gemaakt voor elk werkblad, genoemd in het originele werkboek, het heeft een nieuwe versie gemaakt met WB vooraan. Oké, Joe, toen Joe me dit briefje stuurde, zei hij dat hij deze gegevens naar klanten zou sturen en ik raakte in eerste instantie een beetje in paniek omdat ik zei, wacht even Joe, we zullen een probleem krijgen omdat jij gaat Gary zijn gegevens sturen, toch? Maar dit is, eh, je kent een live, een live dataset, het is een live draaitabel. Al het spul hier, je kunt misschien alle informatie voor andere klanten krijgen, toch? Tjonge, je wilt niet een klant A de informatie sturen voor alle andere klanten. Dat kan een gedoe zijn en eigenlijk, toen ik de shownota herlas, was hij slimmer dan ik, want hij zei:Ik wil ze als pdf-bestanden maken. Ik had zoiets van, oké, nou ja, we hoeven ons geen zorgen te maken over pdf-bestanden, dat is prima, maar wat ik hier aan de macro heb toegevoegd, was de mogelijkheid om waarden te plakken voordat je ze opslaat? WAAR.
Dus je stelt dat gelijk aan TRUE en dat zal dit kleine stukje code hier oproepen, waar we zeggen: If PasteV Then the UsedRange.Copy en dan UsedRange.PasteSpecial (xlPasteValues), UsedRange, in plaats van alle 17 miljard cellen te kopiëren en te plakken , het beperkt het tot goed, de UsedRange.
Oké, dus laten we terugschakelen, dat werkblad verwisselen met de gegevens, CTRL SHIFT S voor splitsen en dan deze nieuwe versie in de rapportendirectory, je zult zien dat het de draaitabel heeft verwijderd en alleen de gegevens daar heeft achtergelaten. Zodat ze niet bij alle gegevens kunnen komen.
Alright, we'll try the other feature. We'll try if we switch from Excel to PDF change the prefix to PDFFileOf, whatever we want there. I won't even try the suffix, something. Alright and then switch to the data, CTRL SHIFT S. Alrighty, so we get the same files PDFFileOf the Worksheet name, something of PDF and we should have just nice little PDFs in there, like that.
Alright so there you have it the.com Worksheet Splitter. Hopefully generic enough, for whatever you need. Download it again from the link there in the YouTube comments. To learn more about VBA, check out this book Excel 2016 VBA and Macros by myself and Tracy ?08:50.640. Click that I on the top right hand corner, to read more about the book.
Joe, uit Californië, en vele anderen hebben gevraagd om een manier om elk werkblad in een ander bestand op te slaan, hetzij als pdf in het geval van Joe, of als Excel-bestand voor het geval je Power Query gaat gebruiken om bestanden te combineren. Dus heb ik daar een leuke kleine Generic Freeware Utility gemaakt. Je kunt het downloaden en proberen.
Ik wil Joe bedanken voor het stellen van die vraag en wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2107.xlsm
