Bekijk video - Hoe elk Excel-blad in afzonderlijke bestanden te splitsen
Als u een Excel-werkmap met veel werkbladen hebt, is er geen gemakkelijke manier om elk van deze bladen in afzonderlijke Excel-bestanden te splitsen en afzonderlijk op te slaan.
Dit kan nodig zijn wanneer u bladen voor verschillende maanden of regio's of producten/klanten hebt en u voor elk blad snel een aparte werkmap wilt hebben (als Excel-bestand of als pdf).
Hoewel er een handmatige manier is om bladen in afzonderlijke werkmappen te splitsen en deze vervolgens op te slaan, is deze inefficiënt en foutgevoelig.
In deze zelfstudie geef ik je een eenvoudige VBA-code die je kunt gebruiken om snel (in een paar seconden) alle werkbladen in hun eigen afzonderlijke bestanden te splitsen en deze vervolgens in een opgegeven map op te slaan.
Splits elk werkblad in een apart Excel-bestand
Stel dat je een werkmap hebt zoals hieronder weergegeven, waar je voor elke maand een werkblad hebt.
Om deze bladen op te splitsen in een apart Excel-bestand, kunt u de onderstaande VBA-code gebruiken:
'Code gemaakt door Sumit Bansal van 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 Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Er zijn een paar dingen die u moet controleren voordat u de bovenstaande VBA-code gebruikt:
- Maak een map waarin u alle resulterende bestanden wilt krijgen.
- Bewaar het hoofd Excel-bestand (met alle werkbladen die u wilt als afzonderlijke bestanden) in deze map.
Zodra u dit hebt gedaan, kunt u de bovenstaande VBA-code in het bestand plaatsen en de code uitvoeren.
De bovenstaande code is zo geschreven dat het de locatie van de map oppikt met behulp van het pad van het bestand (waarin de code wordt uitgevoerd). Daarom is het belangrijk om het bestand eerst in de map op te slaan en vervolgens deze code te gebruiken.
Hoe werkt de VBA-code? - De bovenstaande code gebruikt een eenvoudige For Next-lus die door elk werkblad gaat, een kopie van het werkblad in een Excel-werkmap maakt en deze Excel-werkmap vervolgens opslaat in de opgegeven map (dezelfde die het hoofdbestand heeft met alle vellen).
Hieronder vindt u de stappen om deze VBA-code in de Excel-werkmap te plaatsen (deze zijn hetzelfde voor alle andere methoden die in deze zelfstudie worden getoond):
Waar plaats je deze code?
Hieronder staan de stappen om de code in de Visual Basic Editor te plaatsen waar deze kan worden uitgevoerd:
- Klik op het tabblad Ontwikkelaars.
- Klik in de groep Code op de optie Visual Basic. Hiermee wordt de VB-editor geopend. [U kunt ook de sneltoets gebruiken - ALT + F11]
- Klik in de VB-editor met de rechtermuisknop op een van de objecten van de werkmap waaraan u werkt.
- Beweeg de cursor over de optie Invoegen
- Klik op Module. Dit zal een nieuwe module invoegen
- Dubbelklik op het Module-object. dit opent het codevenster voor de module
- Kopieer de bovenstaande VBA-code en plak deze in het modulecodevenster.
- Selecteer een regel in de code en klik op de groene afspeelknop in de werkbalk om de VBA-macrocode uit te voeren.
De bovenstaande stappen zouden de werkbladen onmiddellijk opsplitsen in afzonderlijke Excel-bestanden en deze opslaan. Het duurt maar een seconde als je minder werkbladen hebt. Als je veel hebt, kan het even duren.
De naam van elk opgeslagen bestand is dezelfde als die van de bladnaam in het hoofdbestand.
Aangezien u een VBA-code in de Excel-werkmap hebt geplaatst, moet u deze opslaan in een .XLSM-indeling (het macro-enabled formaat). Dit zorgt ervoor dat de macro wordt opgeslagen en werkt wanneer u dit bestand vervolgens opent.
Merk op dat ik de lijnen heb gebruikt Application.ScreenUpdating = False en Application.DisplayAlerts = False in de code zodat alles in de backend gebeurt en niets op je scherm ziet gebeuren. Zodra de code wordt uitgevoerd en de bladen zijn gesplitst en deze zijn opgeslagen, zetten we deze terug naar TRUE.
Als best practice wordt aanbevolen om een reservekopie te maken van het hoofdbestand (met de bladen die u wilt splitsen). Dit zorgt ervoor dat u uw gegevens niet verliest voor het geval er iets misgaat of als Excel besluit traag te worden of te crashen.
Elk werkblad splitsen en opslaan als afzonderlijke pdf's
Als u de werkbladen wilt splitsen en deze wilt opslaan als PDF-bestanden in plaats van de Excel-bestanden, kunt u de onderstaande code gebruiken:
'Code gemaakt door Sumit Bansal van 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.ExportAsFixedFormat Type:=xlTypePDF, Bestandsnaam:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Dingen die u moet controleren voordat u deze code gebruikt:
- Maak een map waar u alle resulterende bestanden wilt krijgen.
- Bewaar het hoofd Excel-bestand (met alle werkbladen die u wilt als afzonderlijke bestanden) in deze map.
De bovenstaande code splitst elk blad in het Excel-bestand en slaat het op als een PDF in dezelfde map waarin u het hoofd Excel-bestand hebt opgeslagen.
Splits alleen die werkbladen die een woord/zin bevatten in afzonderlijke Excel-bestanden
Als u veel bladen in een werkmap heeft en u alleen die bladen wilt splitsen met een specifieke tekst erin, dan kunt u dat ook doen.
Stel bijvoorbeeld dat u een Excel-bestand hebt waarin u gegevens voor meerdere jaren hebt en elk blad in het bestand het jaarnummer als prefix heeft. Iets zoals hieronder weergegeven:
Stel nu dat u alle bladen voor 2021-2022 wilt splitsen en deze wilt opslaan als afzonderlijke Excel-bestanden. Om dit te doen, moet u op de een of andere manier de naam van elk werkblad controleren en alleen die bladen met het nummer 2021-2022 moeten worden gesplitst en opgeslagen, en de rest moet onaangeroerd blijven.
Dit kan worden gedaan met behulp van de volgende VBA-macrocode:
'Code gemaakt door Sumit Bansal van 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. Bladen 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
In de bovenstaande code heb ik een variabele TexttoFind gebruikt, die in het begin is toegewezen aan "2020".
De VBA-code gebruikt vervolgens de For Next-lus in VBA om elk werkblad te doorlopen en vervolgens de naam van elk werkblad INSTR-functie te controleren. Deze functie controleert of de naam van het werkblad het woord 2021-2022 bevat of niet. Als dit het geval is, wordt een positienummer geretourneerd waar deze tekst wordt gevonden (in dit geval 2021-2022).
En als het de tekst die we zoeken niet vindt, retourneert het 0.
Dit wordt gebruikt met de ALS Dan-voorwaarde. Dus als een bladnaam de tekst string2021-2022 bevat, wordt deze gesplitst en opgeslagen als een apart bestand. En als het deze tekstreeks niet heeft, zou niet aan de IF-voorwaarde worden voldaan en zou er niets gebeuren.
