Oglejte si video - Kako razdeliti vsak Excel list v ločene datoteke
Če imate Excelov delovni zvezek z veliko delovnimi listi, ni mogoče preprosto razdeliti vsakega od teh listov v ločene Excelove datoteke in jih shraniti ločeno.
To bi lahko bilo potrebno, ko pripravljate liste za različne mesece ali regije ali izdelke/odjemalce in želite hitro pridobiti ločen delovni zvezek za vsak list (kot datoteko Excel ali kot datoteke PDF).
Čeprav obstaja ročni način za razdelitev listov v ločene delovne zvezke in jih nato shranite, je neučinkovit in nagnjen k napakam.
V tej vadnici vam bom predstavil preprosto kodo VBA, s katero lahko hitro (v nekaj sekundah) razdelite vse delovne liste v njihove ločene datoteke in jih nato shranite v katero koli določeno mapo.
Vsak delovni list razdelite v ločeno datoteko Excel
Recimo, da imate delovni zvezek, kot je prikazano spodaj, kjer imate delovni list za vsak mesec.
Če želite te liste razdeliti v ločeno datoteko Excel, lahko uporabite spodnjo kodo VBA:
'Koda, ki jo je ustvaril Sumit Bansal iz trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Za vsak ws v ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Ime datoteke: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Pred uporabo zgornje kode VBA se morate prepričati o nekaj stvareh:
- Ustvarite mapo, kamor želite dobiti vse nastale datoteke.
- V to mapo shranite glavno datoteko Excel (ki vsebuje vse delovne liste, ki jih želite ločiti).
Ko to storite, lahko zgornjo kodo VBA vstavite v datoteko in zaženete kodo.
Zgornja koda je napisana tako, da poišče lokacijo mape s potjo datoteke (v kateri se izvaja koda). Zato je pomembno, da datoteko najprej shranite v mapo in nato uporabite to kodo.
Kako deluje koda VBA - Zgornja koda uporablja preprosto zanko For Next, ki gre skozi vsak delovni list, ustvari kopijo delovnega lista v Excelovem delovnem zvezku in nato shrani ta Excelov delovni zvezek v določeno mapo (ki je enaka glavni datoteki z vsemi listi).
Spodaj so navedeni koraki za umestitev te kode VBA v Excelov delovni zvezek (ti bodo enaki za vse druge metode, prikazane v tej vadnici):
Kam postaviti to kodo?
Spodaj so navedeni koraki za umestitev kode v urejevalnik Visual Basic, kjer jo je mogoče izvesti:
- Kliknite zavihek Razvijalec.
- V skupini Koda kliknite možnost Visual Basic. S tem se odpre urejevalnik VB. [Uporabite lahko tudi bližnjico na tipkovnici - ALT + F11]
- V urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, na katerem delate.
- Premaknite kazalec miške nad možnost Vstavi
- Kliknite na Modul. S tem boste vstavili nov modul
- Dvokliknite predmet Modul. s tem se odpre okno kode za modul
- Kopirajte zgornjo kodo VBA in jo prilepite v okno kode modula.
- Izberite katero koli vrstico v kodi in kliknite zeleni gumb za predvajanje v orodni vrstici, da zaženete kodo makra VBA.
Zgornji koraki bi delovne liste takoj razdelili v ločene Excelove datoteke in jih shranili. Če imate manj delovnih listov, traja le sekundo. Če imate veliko, lahko traja nekaj časa.
Ime vsake shranjene datoteke je enako imenu imena lista v glavni datoteki.
Ker ste kodo VBA vnesli v Excelov delovni zvezek, jo morate shraniti v obliki .XLSM (ki je oblika, ki omogoča makro). To bo zagotovilo, da se makro shrani in deluje, ko naslednjič odprete to datoteko.
Upoštevajte, da sem uporabil vrstice Application.ScreenUpdating = Napačno in Application.DisplayAlerts = Napačno v kodi, tako da se vse dogaja v ozadju in ne vidi stvari, ki se dogajajo na vašem zaslonu. Ko se koda zažene, razdeli liste in jih shrani, jih obrnemo nazaj na TRUE.
Najbolje je, da ustvarite varnostno kopijo glavne datoteke (ki vsebuje liste, ki jih želite razdeliti). Tako boste zagotovili, da ne izgubite svojih podatkov, če gre kaj narobe ali če se Excel odloči, da bo počasen ali se zruši.
Razdelite vsak delovni list in shranite kot ločene datoteke PDF
Če želite razdeliti delovne liste in jih shraniti kot datoteke PDF namesto datotek Excel, lahko uporabite spodnjo kodo:
'Koda, ki jo je ustvaril Sumit Bansal iz trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Za vsak ws v ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.Export Vrsta: = xlTypePDF, Ime datoteke: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Pred uporabo te kode se morate prepričati:
- Ustvarite mapo, kamor želite dobiti vse nastale datoteke.
- V to mapo shranite glavno datoteko Excel (ki vsebuje vse delovne liste, ki jih želite ločiti).
Zgornja koda razdeli vsak list v Excelovi datoteki in jo shrani kot PDF v isto mapo, kamor ste shranili glavno Excelovo datoteko.
Razdelite samo tiste delovne liste, ki vsebujejo besedo/besedno zvezo v ločene datoteke Excel
Če imate v delovnem zvezku veliko listov in želite razdeliti le tiste liste, ki vsebujejo določeno besedilo, lahko to storite tudi.
Recimo, da imate datoteko Excel, v kateri podatke hranite več let, in ima vsak list v datoteki številko leta kot predpono. Nekaj, kot je prikazano spodaj:
Recimo, da želite razdeliti vse liste za obdobje 2021–2022 in jih shraniti kot ločene datoteke Excel. Če želite to narediti, morate nekako preveriti ime vsakega delovnega lista in le tiste liste, ki imajo številko 2021-2022, je treba razdeliti in shraniti, ostale pa pustiti nedotaknjene.
To lahko storite z naslednjo makro kodo VBA:
"Koda, ki jo je ustvaril Sumit Bansal iz 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 vsak ws v tej knjigi. List If InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 Potem ws.Copy Application.ActiveWorkbook.SaveAs Ime datoteke: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End Če je naslednji Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
V zgornji kodi sem uporabil spremenljivko TexttoFind, ki je bila na začetku dodeljena »2020«.
Koda VBA nato uporabi zanko For Next v VBA, da preide skozi vsak delovni list in nato preveri ime vsake funkcije INSTR na delovnem listu. Ta funkcija preveri, ali ima ime delovnega lista besedo 2021-2022 ali ne. Če se to zgodi, bo vrnil številko položaja, kjer najde to besedilo (kar je v tem primeru 2021-2022).
In če ne najde besedila, ki ga iščemo, vrne 0.
To se uporablja s pogojem IF Then. Torej, če ime lista vsebuje besedilni niz2021-2022, bo razdeljeno in shranjeno kot ločena datoteka. In če nima tega besedilnega niza, pogoj IF ne bi bil izpolnjen in nič se ne bi zgodilo.
