Se video - Slik deler du hvert Excel -ark i separate filer
Hvis du har en Excel -arbeidsbok med mange regneark, er det ingen enkel måte å dele hvert av disse arkene i separate Excel -filer og lagre separat.
Dette kan være nødvendig når du ark for forskjellige måneder eller regioner eller produkter/klienter, og du vil raskt få en egen arbeidsbok for hvert ark (som en Excel -fil eller som PDF -filer).
Selv om det er en manuell måte å dele ark i separate arbeidsbøker og deretter lagre det, er det ineffektivt og utsatt for feil.
I denne opplæringen vil jeg gi deg en enkel VBA -kode som du kan bruke til raskt (på få sekunder) å dele alle regnearkene i sine egne separate filer og deretter lagre disse i en spesifisert mappe.
Del hvert regneark i en egen Excel -fil
Anta at du har en arbeidsbok som vist nedenfor, der du har et regneark for hver måned.
For å dele disse arkene i en egen Excel -fil, kan du bruke VBA -koden nedenfor:
'Code Created by Sumit Bansal from 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 Filnavn: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Det er noen ting du må sørge for før du bruker ovennevnte VBA -kode:
- Lag en mappe der du vil få alle de resulterende filene.
- Lagre hoved Excel -filen (som har alle regnearkene du vil ha som separate filer) i denne mappen.
Når du har gjort dette, kan du sette ovennevnte VBA -kode i filen og kjøre koden.
Koden ovenfor er skrevet på en måte at den henter plasseringen av mappen ved å bruke banen til filen (der koden kjøres). Derfor er det viktig å lagre filen i mappen først og deretter bruke denne koden.
Hvordan fungerer VBA -koden - Koden ovenfor bruker en enkel For Next -løkke som går gjennom hvert regneark, oppretter en kopi av regnearket i en Excel -arbeidsbok og deretter lagrer denne Excel -arbeidsboken i den angitte mappen (som er den samme som har hovedfilen med alle ark).
Nedenfor er trinnene for å plassere denne VBA -koden i Excel -arbeidsboken (disse vil være de samme for alle de andre metodene som vises i denne opplæringen):
Hvor skal jeg legge denne koden?
Nedenfor er trinnene for å plassere koden i Visual Basic Editor der den kan kjøres:
- Klikk på Utvikler -fanen.
- Klikk på alternativet Visual Basic i kodegruppen. Dette åpner VB Editor. [Du kan også bruke hurtigtasten - ALT + F11]
- I VB Editor, høyreklikk på et av objektene i arbeidsboken du jobber med.
- Hold markøren over alternativet Sett inn
- Klikk på modul. Dette vil sette inn en ny modul
- Dobbeltklikk på modulobjektet. Dette åpner kodevinduet for modulen
- Kopier VBA -koden som er angitt ovenfor, og lim den inn i modulkodevinduet.
- Velg en hvilken som helst linje i koden, og klikk på den grønne avspillingsknappen på verktøylinjen for å kjøre VBA -makrokoden.
De ovennevnte trinnene vil umiddelbart dele regnearkene i separate Excel -filer og lagre disse. Det tar bare et sekund hvis du har færre antall regneark. Hvis du har mye, kan det ta litt tid.
Navnet på hver lagrede fil er det samme som navnet på arket den hadde i hovedfilen.
Siden du har plassert en VBA-kode i Excel-arbeidsboken, må du lagre dette med et .XLSM-format (som er det makroaktiverte formatet). Dette vil sikre at makroen lagres og fungerer når du åpner denne filen neste.
Legg merke til at jeg har brukt linjene Application.ScreenUpdating = Falsk og Application.DisplayAlerts = Falsk i koden slik at alt skjer i backend og ikke ser ting som skjer på skjermen. Når koden kjører og deler arkene og lagrer disse, setter vi disse tilbake til TRUE.
Som en god praksis anbefales det å lage en sikkerhetskopi av hovedfilen (som har arkene du vil dele). Dette vil sikre at du ikke mister dataene dine hvis noe skulle gå galt eller hvis Excel bestemmer seg for å bli treg eller krasje.
Del hvert regneark og lagre som separate PDF -filer
Hvis du vil dele regnearkene og lagre disse som PDF -filer i stedet for Excel -filene, kan du bruke koden nedenfor:
'Code Created by Sumit Bansal from 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.ExportAsFixed Type: = xlTypePDF, filnavn: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Ting du må sørge for før du bruker denne koden:
- Lag en mappe der du vil få alle de resulterende filene.
- Lagre hoved Excel -filen (som har alle regnearkene du vil ha som separate filer) i denne mappen.
Koden ovenfor deler hvert ark i Excel -filen og lagrer det som en PDF i samme mappe der du har lagret hoved Excel -filen.
Del bare de regnearkene som inneholder et ord/uttrykk i separate Excel -filer
Hvis du har mange ark i en arbeidsbok og du bare vil dele de arkene som har en bestemt tekst i den, kan du også gjøre det.
Anta for eksempel at du har en Excel -fil der du data i flere år, og hvert ark i filen har årstallet som prefiks. Noe som vist nedenfor:
La oss nå si at du vil dele alle arkene for 2020-2022 og lagre disse som separate Excel-filer. For å gjøre dette må du på en eller annen måte kontrollere navnet på hvert regneark, og bare de arkene som har nummeret 2020-2022 skal deles og lagres, og resten skal stå uberørt.
Dette kan gjøres ved å bruke følgende VBA -makrokode:
'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 each ws In ThisWorkbook. Ark Hvis InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 Deretter ws.Copy Application.ActiveWorkbook.SaveAs filnavn: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
I koden ovenfor har jeg brukt en variabel TexttoFind, som har blitt tildelt "2020" i begynnelsen.
VBA -koden bruker deretter For Next -løkken i VBA til å gå gjennom hvert regneark og deretter sjekke navnet på hver INSTR -funksjon for regnearket. Denne funksjonen sjekker om regnearknavnet har ordet2021-2022 i det eller ikke. Hvis den gjør det, vil den returnere et posisjonsnummer der den finner denne teksten (som er2021-2022 i dette tilfellet).
Og hvis den ikke finner teksten vi leter etter, returnerer den 0.
Dette brukes med IF Then -betingelsen. Så hvis et arknavn har teksten string2021-2022 i seg, blir det delt og lagret som en egen fil. Og hvis den ikke har denne tekststrengen, ville IF -betingelsen ikke være oppfylt og ingenting ville skje.
