Podívejte se na video - Jak rozdělit každý list Excelu do samostatných souborů
Pokud máte sešit aplikace Excel s mnoha listy, neexistuje snadný způsob, jak rozdělit každý z těchto listů na samostatné soubory aplikace Excel a uložit je samostatně.
To může být potřeba, když listy pro různé měsíce nebo oblasti nebo produkty/klienty a chcete rychle získat samostatný sešit pro každý list (jako soubor aplikace Excel nebo jako soubory PDF).
Přestože existuje ruční způsob rozdělení listů do samostatných sešitů a jejich uložení, je to neefektivní a náchylné k chybám.
V tomto tutoriálu vám poskytnu jednoduchý kód VBA, který můžete použít k rychlému (během několika sekund) rozdělení všech listů do vlastních samostatných souborů a jejich uložení do libovolné určené složky.
Rozdělte každý list do samostatného souboru aplikace Excel
Předpokládejme, že máte sešit, jak je uvedeno níže, kde máte pracovní list pro každý měsíc.
K rozdělení těchto listů do samostatného souboru aplikace Excel můžete použít níže uvedený kód VBA:
'Kód vytvořil 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ázev souboru: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Před použitím výše uvedeného kódu VBA je třeba zajistit několik věcí:
- Vytvořte složku, do které chcete získat všechny výsledné soubory.
- Do této složky uložte hlavní soubor aplikace Excel (který obsahuje všechny požadované listy jako samostatné soubory).
Jakmile to uděláte, můžete do souboru vložit výše uvedený kód VBA a spustit kód.
Výše uvedený kód je napsán tak, že vyhledává umístění složky pomocí cesty k souboru (ve kterém je kód spuštěn). Proto je důležité soubor nejprve uložit do složky a poté použít tento kód.
Jak funguje kód VBA - Výše uvedený kód používá jednoduchou smyčku For Next, která prochází každým listem, vytvoří kopii listu v sešitu aplikace Excel a poté tento sešit aplikace Excel uloží do zadané složky (což je totéž, co má hlavní soubor se všemi povlečení na postel).
Níže jsou uvedeny kroky k umístění tohoto kódu VBA do sešitu aplikace Excel (budou stejné pro všechny ostatní metody uvedené v tomto kurzu):
Kam tento kód vložit?
Níže jsou uvedeny kroky k vložení kódu do editoru jazyka Visual Basic, kde jej lze spustit:
- Klikněte na kartu Vývojář.
- Ve skupině Kód klikněte na možnost Visual Basic. Tím se otevře editor VB. [Můžete také použít klávesovou zkratku - ALT + F11]
- V editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, na kterém pracujete.
- Umístěte kurzor na možnost Vložit
- Klikněte na Modul. Tím se vloží nový modul
- Poklepejte na objekt Module. tím se otevře okno kódu pro modul
- Zkopírujte výše uvedený kód VBA a vložte jej do okna kódu modulu.
- Vyberte libovolný řádek v kódu a kliknutím na zelené tlačítko přehrávání na panelu nástrojů spusťte kód makra VBA.
Výše uvedené kroky by okamžitě rozdělily listy do samostatných souborů aplikace Excel a uložily je. Trvá to jen sekundu, pokud máte menší počet pracovních listů. Pokud toho máte hodně, může to nějakou dobu trvat.
Název každého uloženého souboru je stejný jako název listu, který měl v hlavním souboru.
Protože jste do sešitu aplikace Excel umístili kód VBA, musíte jej uložit ve formátu .XLSM (což je formát s podporou maker). Tím zajistíte, že se makro uloží a bude fungovat při příštím otevření tohoto souboru.
Všimněte si, že jsem použil řádky Application.ScreenUpdating = False a Application.DisplayAlerts = False v kódu, aby se vše stalo v backendu a nevidělo se, co se děje na vaší obrazovce. Jakmile se kód spustí a rozdělí listy a uloží je, vrátíme je zpět na hodnotu TRUE.
Jako osvědčený postup se doporučuje vytvořit záložní kopii hlavního souboru (který obsahuje listy, které chcete rozdělit). Tím zajistíte, že neztratíte svá data v případě, že se něco pokazí, nebo pokud se Excel rozhodne zpomalit nebo havarovat.
Rozdělit každý list a uložit jako samostatné soubory PDF
V případě, že chcete rozdělit listy a uložit je jako soubory PDF namísto souborů aplikace Excel, můžete použít následující kód:
'Kód vytvořil 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ázev souboru: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Před použitím tohoto kódu se musíte ujistit:
- Vytvořte složku, do které chcete získat všechny výsledné soubory.
- Do této složky uložte hlavní soubor aplikace Excel (který obsahuje všechny požadované listy jako samostatné soubory).
Výše uvedený kód rozdělí každý list v souboru aplikace Excel a uloží jej jako PDF do stejné složky, kam jste uložili hlavní soubor aplikace Excel.
Rozdělte pouze ty listy, které obsahují slovo/frázi, do samostatných souborů aplikace Excel
V případě, že máte v sešitu mnoho listů a chcete rozdělit pouze ty listy, které obsahují konkrétní text, můžete to udělat také.
Předpokládejme například, že máte soubor aplikace Excel, kde data zadáváte více let a každý list v souboru má jako předponu číslo roku. Něco, jak je uvedeno níže:
Nyní řekněme, že chcete rozdělit všechny listy na období 2021-2022 a uložit je jako samostatné soubory aplikace Excel. Chcete-li to provést, musíte nějak zkontrolovat název každého listu a pouze ty listy, které mají číslo 2021-2022, by měly být rozděleny a uloženy a zbytek by měl zůstat nedotčen.
To lze provést pomocí následujícího kódu makra VBA:
'Code Created by Sumit Bansal from trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String Dim TexttoFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For every ws In ThisWorkbook. Sheets If InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 Then ws.Copy Application.ActiveWorkbook.SaveAs Filename: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Ve výše uvedeném kódu jsem použil proměnnou TexttoFind, která byla na začátku přiřazena k „2020“.
Kód VBA poté pomocí smyčky For Next ve VBA projde každý list a poté zkontroluje název každé funkce INSTR listu. Tato funkce kontroluje, zda název listu obsahuje slovo 2021-2022 nebo ne. Pokud ano, vrátí číslo pozice, kde najde tento text (což je v tomto případě 2021-2022).
A pokud nenajde text, který hledáme, vrátí 0.
To se používá s podmínkou IF Then. Pokud tedy název listu obsahuje textový řetězec2021-2022, bude rozdělen a uložen jako samostatný soubor. A pokud tento textový řetězec nemá, podmínka IF by nebyla splněna a nic by se nestalo.
