Pogledajte video - Kako podijeliti svaki Excel list u zasebne datoteke
Ako imate radnu knjigu programa Excel s mnogo radnih listova, ne postoji jednostavan način za razdvajanje svakog od ovih listova u zasebne datoteke programa Excel i spremanje zasebno.
To bi moglo biti potrebno kada izrađujete listove za različite mjesece ili regije ili proizvode/klijente i želite brzo dobiti zasebnu radnu knjigu za svaki list (kao Excel datoteku ili kao PDF).
Iako postoji ručni način za razdvajanje listova u zasebne radne knjige i njihovo spremanje, to je neučinkovito i sklono je pogreškama.
U ovom ću vam tutorijalu dati jednostavan VBA kôd koji možete koristiti za brzo (u nekoliko sekundi) podjelu svih radnih listova u njihove zasebne datoteke, a zatim ih spremite u bilo koju navedenu mapu.
Podijelite svaki radni list u zasebnu Excel datoteku
Pretpostavimo da imate radnu knjigu kao što je prikazano ispod gdje imate radni list za svaki mjesec.
Da biste podijelili ove listove u zasebnu Excel datoteku, možete upotrijebiti donji VBA kôd:
'Kôd koji je stvorio Sumit Bansal s trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Za svaki ws u ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveApp. Naziv datoteke: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Prije korištenja gornjeg VBA koda morate se uvjeriti u nekoliko stvari:
- Napravite mapu u koju želite dobiti sve rezultirajuće datoteke.
- Spremite glavnu datoteku programa Excel (koja sadrži sve radne listove koje želite kao zasebne datoteke) u ovu mapu.
Nakon što to učinite, tada možete staviti gornji VBA kôd u datoteku i pokrenuti kôd.
Gornji kôd napisan je na način da preuzima mjesto mape pomoću putanje datoteke (u kojoj se kôd izvodi). Zbog toga je važno prvo spremiti datoteku u mapu, a zatim koristiti ovaj kôd.
Kako funkcionira VBA kod - Gornji kôd koristi jednostavnu petlju For Next koja prolazi kroz svaki radni list, stvara kopiju radnog lista u radnoj knjizi programa Excel, a zatim sprema ovu radnu knjigu programa Excel u navedenu mapu (koja je ista koja ima glavnu datoteku sa svim datotekama listova).
U nastavku su navedeni koraci za postavljanje ovog VBA koda u radnu knjigu programa Excel (isti će biti za sve ostale metode prikazane u ovom vodiču):
Gdje staviti ovaj kod?
U nastavku su navedeni koraci za postavljanje koda u uređivač Visual Basic gdje se može izvršiti:
- Kliknite karticu Developer.
- U grupi Kod kliknite opciju Visual Basic. Ovo će otvoriti VB Editor. [Također možete koristiti tipkovnički prečac - ALT + F11]
- U VB Editoru desnom tipkom miša kliknite bilo koji objekt radne knjige na kojem radite.
- Zadržite pokazivač iznad opcije Umetanje
- Kliknite na Modul. Ovo će umetnuti novi modul
- Dvaput kliknite objekt Modul. ovo će otvoriti prozor koda za modul
- Kopirajte gore navedeni VBA kôd i zalijepite ga u prozor koda modula.
- Odaberite bilo koji redak u kodu i kliknite zeleni gumb za reprodukciju na alatnoj traci za pokretanje VBA makro koda.
Gore navedeni koraci odmah bi podijelili radne listove u zasebne Excel datoteke i spremili ih. Treba vam samo sekunda ako imate manji broj radnih listova. U slučaju da imate puno, može potrajati neko vrijeme.
Naziv svake spremljene datoteke isti je kao i naziv lista koji je imao u glavnoj datoteci.
Budući da ste VBA kôd stavili u radnu knjigu programa Excel, morate ga spremiti u .XLSM formatu (koji je format s omogućenim makronaredbama). To će osigurati spremanje i funkcioniranje makronaredbe kada sljedeći put otvorite ovu datoteku.
Imajte na umu da sam koristio linije Application.ScreenUpdating = Netačno i Application.DisplayAlerts = Netačno u kodu tako da se sve događa u pozadini i da ne vidite stvari koje se događaju na vašem zaslonu. Nakon što se kôd pokrene, podijeli listove i spremi ih, vraćamo ih na TRUE.
Kao najbolja praksa, preporučuje se stvaranje sigurnosne kopije glavne datoteke (koja sadrži listove koje želite podijeliti). To će osigurati da ne izgubite svoje podatke u slučaju da nešto pođe po zlu ili ako Excel odluči usporiti ili se srušiti.
Podijelite svaki radni list i spremite kao zasebne PDF -ove
U slučaju da želite podijeliti radne listove i spremiti ih kao PDF datoteke umjesto Excel datoteka, možete upotrijebiti donji kod:
'Kôd koji je napravio Sumit Bansal s trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Za svaki ws u ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.Export Vrsta: = xlTypePDF, Naziv datoteke: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Stvari koje morate provjeriti prije korištenja ovog koda:
- Napravite mapu u koju želite dobiti sve rezultirajuće datoteke.
- Spremite glavnu datoteku programa Excel (koja sadrži sve radne listove koje želite kao zasebne datoteke) u ovu mapu.
Gornji kôd je podijelio svaki list u Excel datoteci i spremio ga kao PDF u istu mapu u koju ste spremili glavnu Excel datoteku.
Podijelite samo one radne listove koji sadrže riječ/izraz u zasebne Excel datoteke
U slučaju da imate mnogo listova u radnoj knjizi i želite podijeliti samo one listove koji u sebi imaju određeni tekst, to možete učiniti i vi.
Na primjer, pretpostavimo da imate Excel datoteku u kojoj imate podatke za više godina, a svaki list u datoteci ima broj godine kao prefiks. Nešto kao što je prikazano ispod:
Recimo da želite podijeliti sve listove za razdoblje 2021.-2022. I spremiti ih kao zasebne Excel datoteke. Da biste to učinili, morate nekako provjeriti naziv svakog radnog lista i samo one listove koji imaju broj 2021-2022 treba podijeliti i spremiti, a ostale ostaviti netaknutima.
To se može učiniti pomoću sljedećeg VBA makro koda:
'Kôd stvorio Sumit Bansal s trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String Dim TextToFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Za svaki ws u ovoj radnoj knjizi. Tablice If InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 Zatim ws.Copy Application.ActiveWorkbook.SaveAs Naziv datoteke: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = Istinska aplikacija.ScreenUpdating = Istinita završna pod
U gornjem kodu koristio sam varijablu TexttoFind koja je u početku dodijeljena “2020”.
VBA kôd zatim koristi petlju For Next u VBA -i za prolazak kroz svaki radni list, a zatim provjerava naziv svakog INSTR funkcije svakog radnog lista. Ova funkcija provjerava ima li naziv radnog lista riječ 2021-2022 ili ne. Ako to učini, vratit će broj pozicije na kojoj nalazi ovaj tekst (što je u ovom slučaju 2021.-2022.).
A ako ne pronađe tekst koji tražimo, vraća 0.
Ovo se koristi s uvjetom IF Then. Dakle, ako naziv lista ima tekstni niz2021-2022 u sebi, bit će podijeljen i spremljen kao zasebna datoteka. A ako nema ovaj tekstualni niz, uvjet IF ne bi bio ispunjen i ništa se ne bi dogodilo.
