Pozrite si video - Ako rozdeliť každý list programu Excel na samostatné súbory
Ak máte zošit programu Excel s mnohými pracovnými listami, neexistuje jednoduchý spôsob, ako rozdeliť každý z týchto hárkov do samostatných súborov programu Excel a uložiť ich osobitne.
To môže byť potrebné, keď listujete pre rôzne mesiace alebo oblasti alebo produkty/klientov a chcete rýchlo získať samostatný zošit pre každý list (ako súbor programu Excel alebo ako súbory PDF).
Aj keď existuje manuálny spôsob, ako rozdeliť listy do samostatných zošitov a potom ich uložiť, je neefektívny a náchylný na chyby.
V tomto návode vám poskytnem jednoduchý kód VBA, ktorý môžete použiť na rýchle (za niekoľko sekúnd) rozdelenie všetkých pracovných hárkov do vlastných samostatných súborov a ich uloženie do ľubovoľného určeného priečinka.
Rozdeľte každý pracovný hárok do samostatného súboru programu Excel
Predpokladajme, že máte pracovný zošit, ako je uvedené nižšie, kde máte pracovný list na každý mesiac.
Na rozdelenie týchto hárkov do samostatného súboru programu Excel môžete použiť nasledujúci kód VBA:
'Kód vytvoril Sumit Bansal z trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Názov súboru: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Pred použitím vyššie uvedeného kódu VBA je potrebné zaistiť niekoľko vecí:
- Vytvorte priečinok, do ktorého chcete získať všetky výsledné súbory.
- Do tohto priečinka uložte hlavný súbor programu Excel (ktorý obsahuje všetky požadované pracovné hárky ako samostatné súbory).
Akonáhle to máte hotové, môžete vložiť vyššie uvedený kód VBA do súboru a spustiť kód.
Vyššie uvedený kód je napísaný spôsobom, ktorý zisťuje umiestnenie priečinka pomocou cesty k súboru (v ktorom je kód spustený). Preto je dôležité súbor najskôr uložiť do priečinka a potom použiť tento kód.
Ako funguje kód VBA - Vyššie uvedený kód používa jednoduchú slučku For Next, ktorá prechádza každým pracovným listom, vytvorí kópiu pracovného hárka v zošite programu Excel a potom tento zošit programu Excel uloží do zadaného priečinka (ktorý je rovnaký, ako v hlavnom súbore so všetkými súbormi listy).
Nasledujú kroky na vloženie tohto kódu VBA do zošita programu Excel (budú rovnaké pre všetky ostatné metódy uvedené v tomto návode):
Kam vložiť tento kód?
Nasledujú kroky na vloženie kódu do editora jazyka Visual Basic, kde ho je možné vykonať:
- Kliknite na kartu Vývojár.
- V skupine Kód kliknite na možnosť Visual Basic. Tým sa otvorí editor VB. [Môžete tiež použiť klávesovú skratku - ALT + F11]
- V editore VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita, na ktorom pracujete.
- Ukážte kurzorom na možnosť Vložiť
- Kliknite na modul. Tým sa vloží nový modul
- Dvakrát kliknite na objekt Module. tým sa otvorí okno s kódom pre modul
- Skopírujte vyššie uvedený kód VBA a prilepte ho do okna kódu modulu.
- Vyberte ľubovoľný riadok v kóde a kliknutím na zelené tlačidlo prehrávania na paneli s nástrojmi spustíte kód makra VBA.
Vyššie uvedené kroky by okamžite rozdelili pracovné hárky do samostatných súborov programu Excel a uložili ich. Ak máte menší počet pracovných hárkov, trvá to iba sekundu. Ak máte veľa, môže to chvíľu trvať.
Názov každého uloženého súboru je rovnaký ako názov listu, ktorý mal v hlavnom súbore.
Pretože ste vložili kód VBA do zošita programu Excel, musíte ho uložiť vo formáte .XLSM (čo je formát s povolenými makrami). Zaistíte tým, aby bolo makro uložené a fungovalo pri nasledujúcom otvorení tohto súboru.
Všimnite si toho, že som použil riadky Application.ScreenUpdating = False a Application.DisplayAlerts = False v kóde, aby sa všetko dialo v backende a aby ste nevideli, čo sa deje na vašej obrazovke. Akonáhle sa kód spustí a rozdelí hárky a uloží ich, vrátime ich späť na PRAVDU.
Ako osvedčený postup sa odporúča vytvoriť záložnú kópiu hlavného súboru (ktorý obsahuje listy, ktoré chcete rozdeliť). Zaistíte tak, že nestratíte svoje údaje v prípade, že sa niečo pokazí alebo ak sa Excel rozhodne spomaliť alebo zlyhať.
Rozdeľte každý pracovný hárok a uložte ho ako samostatné súbory PDF
V prípade, že chcete pracovné hárky rozdeliť a uložiť ich ako súbory PDF namiesto súborov programu Excel, môžete použiť nasledujúci kód:
'Kód vytvoril Sumit Bansal z trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.ExportAsFixedFor Typ: = xlTypePDF, názov súboru: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Pred použitím tohto kódu sa musíte uistiť:
- Vytvorte priečinok, do ktorého chcete získať všetky výsledné súbory.
- Do tohto priečinka uložte hlavný súbor programu Excel (ktorý obsahuje všetky požadované pracovné hárky ako samostatné súbory).
Vyššie uvedený kód rozdelil každý list v súbore Excel a uložil ho ako PDF do rovnakého priečinka, kam ste uložili hlavný súbor programu Excel.
Rozdeľte iba tie pracovné listy, ktoré obsahujú slovo/frázu, do oddelených súborov programu Excel
V prípade, že máte v zošite veľa hárkov a chcete rozdeliť iba tie listy, v ktorých je konkrétny text, môžete to urobiť tiež.
Predpokladajme napríklad, že máte súbor programu Excel, v ktorom údaje zadávate za niekoľko rokov, a v každom hárku súboru je ako predpona uvedené číslo roku. Niečo, ako je uvedené nižšie:
Teraz povedzme, že chcete rozdeliť všetky listy na roky 2021-2022 a uložiť ich ako samostatné súbory programu Excel. Aby ste to urobili, musíte nejako skontrolovať názov každého pracovného hárka a iba tie listy, ktoré majú číslo 2021-2022, by mali byť rozdelené a uložené a ostatné by mali zostať nedotknuté.
To je možné vykonať pomocou nasledujúceho kódu makra VBA:
'Kód vytvoril Sumit Bansal z trumpexcel.com Sub SplitEachWorksheet () Dim FPath ako reťazec Dim TexttoFind ako reťazec TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Every ws In ThisWorkbook. Hárky If InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 Potom ws.Copy Application.ActiveWorkbook.SaveAs Názov súboru: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Vo vyššie uvedenom kóde som použil premennú TexttoFind, ktorá bola na začiatku priradená k „2020“.
Kód VBA potom pomocou slučky For Next vo VBA prejde každým pracovným listom a potom skontroluje názov každej funkcie INSTR pracovného hárka. Táto funkcia kontroluje, či je v názve pracovného hárka uvedené slovo 2021-2022 alebo nie. Ak áno, vráti číslo pozície, kde nájde tento text (čo je v tomto prípade 2021-2022).
A ak nenájde text, ktorý hľadáme, vráti 0.
Toto sa používa s podmienkou IF Then. Ak teda názov hárka obsahuje textový reťazec2021-2022, bude rozdelený a uložený ako samostatný súbor. A ak nemá tento textový reťazec, podmienka IF by nebola splnená a nič by sa nestalo.
