Máte zošit s mnohými pracovnými listami. Chcete odoslať každý pracovný hárok inej osobe. Dnes makro na rozdelenie týchto údajov.
Pozeraj video
- Joe + iní hľadajú spôsob, ako uložiť každý pracovný hárok do iného súboru
- Užitočné pre Power Query alebo po použití možnosti Zobraziť stránky filtra prehľadov
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2107 - Rozdeľte každý pracovný hárok do nového zošita
Ahoj vitaj späť. Ja som a netcast. Som Bill Jelen.
Pozadu v hlave som vedel, že to musím robiť už dlho, ale dva nedávne podcasty to skutočne priniesli, priniesli domov.
Len nedávno v epizóde 2106, kde sme vytvárali PDF zo všetkých kombinácií výrezov. Neskor v tejto epizóde som ukázal alternatívnu metódu, kde vytvárame veľa kontingenčných správ, ale dáva ich všetky do rovnakého zošita a dostal som e-mail od Joe v Kalifornii, ktorý hovorí dobre, pozrite sa, že musím každý pracovný hárok poslať inému zákazníkovi a to isté, na mojich živých seminároch Power Excel, kde ukážem tento trik, hovoria ľudia, no nie, nechceme všetko v rovnakom pracovnom zošite, chceme ho osobitne a potom je to pravdepodobne ešte dôležitejšie. epizóda 2077, kde som hovoril o tom, ako má teraz Power Query schopnosť kombinovať všetky súbory programu Excel do priečinka, však? A toto je zázračné. Funguje to výborne. Ak ste mali 400 súborov Excel, každý s jedným pracovným hárkom, zhromaždí všetky tieto údaje zo všetkých týchto pracovných hárkov a umiestni ich do jednej mriežky.Čo je úžasné, ale keby sme mali takmer rovnaký problém. Jeden zošit so 400 pracovnými listami? Nemôže to urobiť, správne. S tým sa nedokáže vyrovnať - zatiaľ. Správne, takže práve teraz, 1. júla 2017, sa s tým nemôže vyrovnať. Možno to za šesť mesiacov zvládne, ale momentálne to musia byť pracovné zošity na jeden list.
Potrebujeme teda spôsob, ako byť schopný rozdeliť veci do jednotlivých súborov. Dobre, poďme to teda nastaviť. Máme zošit, ktorý som urobil v roku 2106, kde máme údaje a potom pôvodnú kontingenčnú tabuľku. Prejdeme do časti Analyzovať, Možnosti, Zobraziť stránky filtra prehľadov a zobraziť stránky kľúča a vytvorí pre mňa množstvo rôznych pracovných hárkov a Chcem zobrať tieto pracovné listy a vytvoriť každý z nich je samostatný súbor, ale aj keď to máme, existujú veci ako Sheet2 a Data, ktoré nechcem rozdeliť.
Dobre? A samozrejme pre každého jedného človeka bude ten, ten zoznam pracovných listov, ktorý nechceme rozdeliť, iný, ale budem hádať, že takmer každý má nejaké pracovné listy, ktoré nemá. “ Nechcem sa rozdeliť.
Dobre, takže tu je pomôcka, ktorú si budete môcť stiahnuť. Rozdeľovač pracovných listov a tu mám sekciu v stĺpci B a je to naozaj jediná vec v stĺpci B, kde môžete uviesť zoznam tých pracovných listov, ktoré nechcete rozdeliť. Môžu to byť aj viac ako dva. Môžete ich tu vyplniť toľko, koľko chcete. Môžete vložiť nové riadky a môj lacný spôsob, nechcel som ich musieť prechádzať v makre, takže mimo vášho pohľadu tu, mám miesto, kde môže makro napísať aktuálny názov pracovného hárka a potom jednoduchý malý VÝHĽAD. Píše sa v ňom, choďte hľadať tento pracovný hárok, na ktorom práve pracujeme, či je koniec v stĺpci B, a ak áno, budeme vedieť, že je to ten, ktorý nechceme exportovať.
V poriadku a potom znova, aby to bolo čo najobecnejšie, mám tu niekoľko pomenovaných rozsahov, moju cestu, moju predponu, moju príponu, môj typ a moju vložku. Dobre, takže prídete na to, kam chcete tieto veci smerovať. c: Správy . Chcem, aby každý súbor mal názov hárku, ale pred názov hárku vložím predponu WB, File Suffix a nič, a potom tu máš na výber: PDF alebo XLSX.
Takže začneme s XLSX, o týchto hodnotách vloženia si povieme, ako ich uložíme neskôr. V poriadku a momentálne je to prvá verzia z 1. júla 2017. Ak to vylepšíme, nahradím to na webovej stránke a webovú stránku nájdete dole v popise služby YouTube. Dobre, takže tu je ukážka, ako to bude fungovať. Je to súbor XLSM. Musíte sa teda ubezpečiť, že sú povolené makrá. alt = "" T, M, S, kvôli bezpečnosti musíte byť minimálne na tejto alebo nižšej úrovni. Ak ste na vrchole, musíte to zmeniť, zatvorí zošit, znova sa otvorí. Keď otvoríte zošit, povie sa, hej, ste ochotní akceptovať tu makrá a už to vôbec nie je veľké makro: šesťdesiatosem riadkov kódu a veľa z nich sa zaoberá iba získavaním hodnôt z ponuky List,aké sú teraz premenné.
Dôležité tu však je, že to bude fungovať na ActiveWorkbooku. Prejdete teda na zošit, ktorý obsahuje údaje, a potom ho spustíte stlačením klávesu CTRL SHIFT S, ktorý rozpozná ActiveWorkbook a bude to ten, ktorý rozdelí. Chytí („MyPath“) a je to len preto, že vždy zabudnem vložiť spätné lomítko, ak posledný znak nie je spätné lomítko, potom pridám spätné lomítko a tu dole je to skutočná práca.
Pre každý pracovný hárok, v origináli, v aktívnom WBO. Pracovné listy, otestujeme, či je tam hore a v stĺpci B. Ak je, ak nie, exportujeme tento hárok a ja milujem tento riadok kódu. WS.copy hovorí, že keď vezmem tento zošit, tento pracovný hárok, z tohto veľkého zošita s, poznáte 20 alebo 400 pracovných hárkov a ideme na WS.copy, ktorý z neho urobí kópiu a presunie ju do nového pracovného zošita. a vieme, vieme, že tento nový zošit sa teraz stane aktívnym zošitom v makre a v tomto zošite je samozrejme iba jeden hárok a tento hárok je aktívnym hárkom.
Takže mám pravdu, tu môžem zistiť názov zošita. Nastavte to, Použiť na túto premennú objektu, Nový zošit, Nový pracovný hárok a neskôr, keď budem musieť zavrieť, môžem po vykonaní práce urobiť WBN.close. Nový názov súboru zisťujeme pomocou všetkých premenných. Tento súbor zabite, ak už existuje, a ak je to súbor Excel, urobíme uloženie ako, ak je to PDF.
Mimochodom, tento kód PDF funguje iba v systéme Windows, ak používate počítač Mac, je nám ľúto, budete musieť ísť niekde inde, aby ste zistili ekvivalentný kód Mac. Nemám Mac. Viem, že existuje spôsob, ako uložiť PDF na počítači Mac. Viem, že kód je iný. Budete musieť prísť na to, že jeden z nich, alebo sa vrátiť k skutočnému programu Excel v systéme Windows a potom sme hotoví, zatvoríme.
Dobre, takže je to len také jednoduché malé makro, prepnite tu na náš dátový zošit, ktorý obsahuje všetky pracovné hárky. Je tu 20 rôznych pracovných listov, plus dva, ktoré nechcem robiť, a potom CTRL SHIFT S takto a budeme sledovať, ako bliká, keď vytvára každý z nich. Tam sme: 21 vytvorených súborov.
Poďme sa pozrieť na Windows Explorer a tu je môj OS (C :) Reports, ktorý vytvoril pre každý pracovný hárok, pomenovaný v pôvodnom zošite, vytvoril novú verziu s WB vpredu. Dobre, Joe, keď mi Joe poslal túto poznámku, povedal, že pošle tieto údaje zákazníkom, a ja som najskôr trochu spanikáril, pretože som povedal, počkaj chvíľu, Joe, budeme mať problém, pretože ty idem poslať Garyho, jeho dáta, však? Ale toto je, ach, poznáte živý prenos, živý súbor údajov, je to živý kontingenčný stôl. Všetko tu uvedené, môžete získať všetky informácie o ďalších podobných zákazníkoch, že? Chlapče, nechcete poslať zákazníkovi informáciu o všetkých ostatných zákazníkoch. To by mohlo byť trápenie a vlastne, keď som si znovu prečítal poznámku k relácii, bol múdrejší ako ja, pretože povedal:Chcem ich vytvoriť ako súbory PDF. Bol som rád, dobre, dobre, potom sa nemusíme starať o súbory PDF, to je v poriadku, ale čo som sem pridal, do makra bola schopnosť povedať Paste Values Before Saving? PRAVDA.
Takže nastavíte to na TRUE a to vyvolá tento malý kúsok kódu tu, kde hovoríme, že ak PasteV, potom UsedRange.Copy a potom UsedRange.PasteSpecial (xlPasteValues), UsedRange, namiesto kopírovania a vkladania všetkých 17 miliárd buniek , obmedzí to dobre na UsedRange.
Dobre, tak poďme prepnúť späť, prepnite ten pracovný hárok, ktorý obsahuje údaje, CTRL SHIFT S na rozdelenie a potom táto nová verzia v adresári prehľadov, uvidíte, že sa zbavil kontingenčnej tabuľky a nechal tam iba údaje. Takže tak nemôžu získať všetky údaje.
Dobre, skúsime ďalšiu funkciu. Pokúsime sa, či prejdeme z Excelu do PDF, zmeníme predponu na PDFFileOf, nech už tam chceme čokoľvek. Ani príponu neskúsim, niečo. V poriadku a potom prepnúť na údaje, CTRL SHIFT S. V poriadku, takže dostaneme rovnaké súbory PDFFileOf the worksheet name, something of PDF and we should have just nice small PDFs like that that.
V poriadku, takže tu máte Splitter pracovného listu the.com. Dúfajme, že dostatočne všeobecné, na všetko, čo potrebujete. Stiahnite si ju znova z odkazu v komentároch YouTube. Ak sa chcete dozvedieť viac informácií o VBA, pozrite si túto knihu Excel 2016 VBA a makra, sám a Tracy? 08: 50,640. Kliknutím na ikonu I v pravom hornom rohu si prečítate viac o knihe.
Joe z Kalifornie a veľa ďalších ľudí požiadalo o spôsob uloženia každého pracovného hárka do iného súboru, buď ako PDF v Joeovom prípade, alebo ako súbor Excel, ak chcete na kombináciu súborov použiť Power Query. Takže som vytvoril pekný malý Generic Freeware Utility. Môžete si ich stiahnuť a vyskúšať.
Chcem poďakovať Joeovi za to, že položil túto otázku, a chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2107.xlsm
