Video ansehen - So teilen Sie jede Excel-Tabelle in separate Dateien auf
Wenn Sie eine Excel-Arbeitsmappe mit vielen Arbeitsblättern haben, gibt es keine einfache Möglichkeit, jedes dieser Blätter in separate Excel-Dateien aufzuteilen und separat zu speichern.
Dies kann erforderlich sein, wenn Sie Arbeitsblätter für verschiedene Monate oder Regionen oder Produkte/Kunden erstellen und schnell eine separate Arbeitsmappe für jedes Arbeitsblatt erhalten möchten (als Excel-Datei oder als PDF).
Obwohl es eine manuelle Möglichkeit gibt, Blätter in separate Arbeitsmappen aufzuteilen und dann zu speichern, ist dies ineffizient und fehleranfällig.
In diesem Tutorial gebe ich Ihnen einen einfachen VBA-Code, mit dem Sie schnell (in wenigen Sekunden) alle Arbeitsblätter in ihre eigenen separaten Dateien aufteilen und diese dann in einem beliebigen angegebenen Ordner speichern können.
Teilen Sie jedes Arbeitsblatt in eine separate Excel-Datei auf
Angenommen, Sie haben eine Arbeitsmappe wie unten gezeigt, in der Sie für jeden Monat ein Arbeitsblatt haben.
Um diese Blätter in eine separate Excel-Datei aufzuteilen, können Sie den folgenden VBA-Code verwenden:
'Code erstellt von Sumit Bansal von 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
Es gibt ein paar Dinge, die Sie sicherstellen müssen, bevor Sie den obigen VBA-Code verwenden:
- Erstellen Sie einen Ordner, in dem Sie alle resultierenden Dateien erhalten möchten.
- Speichern Sie die Excel-Hauptdatei (die alle Arbeitsblätter enthält, die Sie als separate Dateien haben möchten) in diesem Ordner.
Sobald Sie dies getan haben, können Sie den obigen VBA-Code in die Datei einfügen und den Code ausführen.
Der obige Code ist so geschrieben, dass er den Speicherort des Ordners anhand des Pfads der Datei (in der der Code ausgeführt wird) ermittelt. Aus diesem Grund ist es wichtig, die Datei zuerst im Ordner zu speichern und dann diesen Code zu verwenden.
Wie funktioniert der VBA-Code? - Der obige Code verwendet eine einfache For Next-Schleife, die jedes Arbeitsblatt durchläuft, eine Kopie des Arbeitsblatts in einer Excel-Arbeitsmappe erstellt und diese Excel-Arbeitsmappe dann im angegebenen Ordner speichert (derselbe, der die Hauptdatei mit allen enthält) Blätter).
Im Folgenden finden Sie die Schritte zum Platzieren dieses VBA-Codes in der Excel-Arbeitsmappe (diese sind für alle anderen in diesem Tutorial gezeigten Methoden gleich):
Wo soll dieser Code abgelegt werden?
Im Folgenden sind die Schritte aufgeführt, um den Code im Visual Basic-Editor zu platzieren, wo er ausgeführt werden kann:
- Klicken Sie auf die Registerkarte Entwickler.
- Klicken Sie in der Gruppe Code auf die Option Visual Basic. Dies öffnet den VB-Editor. [Sie können auch die Tastenkombination verwenden - ALT + F11]
- Klicken Sie im VB-Editor mit der rechten Maustaste auf eines der Objekte der Arbeitsmappe, an der Sie arbeiten.
- Bewegen Sie den Cursor über die Option Einfügen
- Klicken Sie auf Modul. Dadurch wird ein neues Modul eingefügt
- Doppelklicken Sie auf das Modul-Objekt. Dadurch wird das Codefenster für das Modul geöffnet
- Kopieren Sie den oben bereitgestellten VBA-Code und fügen Sie ihn in das Modulcodefenster ein.
- Wählen Sie eine beliebige Zeile im Code aus und klicken Sie auf die grüne Wiedergabeschaltfläche in der Symbolleiste, um den VBA-Makrocode auszuführen.
Die obigen Schritte würden die Arbeitsblätter sofort in separate Excel-Dateien aufteilen und diese speichern. Es dauert nur eine Sekunde, wenn Sie weniger Arbeitsblätter haben. Wenn Sie viel haben, kann es einige Zeit dauern.
Der Name jeder gespeicherten Datei ist derselbe wie der Blattname in der Hauptdatei.
Da Sie einen VBA-Code in der Excel-Arbeitsmappe platziert haben, müssen Sie diesen im .XLSM-Format (das ist das makrofähige Format) speichern. Dadurch wird sichergestellt, dass das Makro gespeichert wird und funktioniert, wenn Sie diese Datei das nächste Mal öffnen.
Beachten Sie, dass ich die Zeilen verwendet habe Application.ScreenUpdating = False und Application.DisplayAlerts = False im Code, damit alles im Backend passiert und nichts auf Ihrem Bildschirm passiert. Sobald der Code ausgeführt und die Blätter geteilt und gespeichert wurde, setzen wir diese wieder auf WAHR.
Als bewährte Methode wird empfohlen, eine Sicherungskopie der Hauptdatei (die die Blätter enthält, die Sie teilen möchten) zu erstellen. Dies stellt sicher, dass Sie Ihre Daten nicht verlieren, falls etwas schief geht oder Excel beschließt, langsam zu werden oder abzustürzen.
Jedes Arbeitsblatt aufteilen und als separate PDFs speichern
Falls Sie die Arbeitsblätter aufteilen und diese als PDF-Dateien anstelle der Excel-Dateien speichern möchten, können Sie den folgenden Code verwenden:
'Code erstellt von Sumit Bansal von 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 Typ:=xlTypePDF, Dateiname:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Dinge, die Sie sicherstellen müssen, bevor Sie diesen Code verwenden:
- Erstellen Sie einen Ordner, in dem Sie alle resultierenden Dateien erhalten möchten.
- Speichern Sie die Excel-Hauptdatei (die alle Arbeitsblätter enthält, die Sie als separate Dateien haben möchten) in diesem Ordner.
Der obige Code teilt jedes Blatt in der Excel-Datei auf und speichert es als PDF im selben Ordner, in dem Sie die Haupt-Excel-Datei gespeichert haben.
Teilen Sie nur die Arbeitsblätter, die ein Wort/eine Phrase enthalten, in separate Excel-Dateien auf
Falls Sie viele Blätter in einer Arbeitsmappe haben und nur die Blätter aufteilen möchten, die einen bestimmten Text enthalten, können Sie dies auch tun.
Angenommen, Sie haben eine Excel-Datei mit Daten für mehrere Jahre und jedes Blatt in der Datei hat die Jahreszahl als Präfix. Etwas wie unten gezeigt:
Nehmen wir nun an, Sie möchten alle Blätter für 2021-2022 aufteilen und diese als separate Excel-Dateien speichern. Um dies zu tun, müssen Sie den Namen jedes Arbeitsblatts irgendwie überprüfen und nur die Blätter mit der Nummer 2021-2022 sollten geteilt und gespeichert werden, und der Rest sollte unberührt bleiben.
Dies kann mit dem folgenden VBA-Makrocode erfolgen:
'Code erstellt von Sumit Bansal von 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. 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
Im obigen Code habe ich eine Variable TexttoFind verwendet, die am Anfang mit „2020“ belegt wurde.
Der VBA-Code verwendet dann die For Next-Schleife in VBA, um jedes Arbeitsblatt durchzugehen und dann den Namen jeder Arbeitsblatt-INSTR-Funktion zu überprüfen. Diese Funktion prüft, ob der Arbeitsblattname das Wort 2021-2022 enthält oder nicht. Wenn dies der Fall ist, wird eine Positionsnummer zurückgegeben, an der dieser Text gefunden wird (in diesem Fall 2021-2022).
Und wenn es den gesuchten Text nicht findet, gibt es 0 zurück.
Dies wird mit der IF Then-Bedingung verwendet. Wenn also ein Blattname den Textstring2021-2022 enthält, wird er geteilt und als separate Datei gespeichert. Und wenn es diese Textzeichenfolge nicht enthält, wäre die IF-Bedingung nicht erfüllt und es würde nichts passieren.
