Tutorials

Excel-VBA Tutorial: Arbeitsblatt nach Spaltenwert in mehrere Tabellenblätter aufteilen

Melden

Hier ist ein ausführliches Tutorial, wie du ein Excel-Arbeitsblatt basierend auf den Werten einer bestimmten Spalte automatisch in mehrere Arbeitsblätter aufteilen kannst.

Dieses Tutorial zeigt dir, wie du mit einem VBA-Makro Datenmengen effizient organisierst. Wenn du beispielsweise eine Liste mit Verkaufsdaten hast, kannst du für jeden Vertriebsmitarbeiter oder jede Region automatisch ein eigenes Tabellenblatt erstellen lassen.

1. Voraussetzungen

Damit das Makro funktioniert, stelle sicher, dass:

  1. Deine Datei als Excel-Arbeitsmappe mit Makros (.xlsm) gespeichert ist.
  2. Die Registerkarte Entwicklertools im Menüband sichtbar ist (Rechtsklick auf das Menüband > Menüband anpassen > Haken bei Entwicklertools).

2. Der VBA-Code

Kopiere den folgenden Code. Er ist so geschrieben, dass er flexibel angepasst werden kann.

Sub SpalteAufteilenInBlaetter()
    Dim wsQuelle As Worksheet
    Dim wsZiel As Worksheet
    Dim letzteZeile As Long
    Dim spalteIndex As Integer
    Dim bereich As Range
    Dim eindeutigeWerte As Collection
    Dim zelle As Range
    Dim wert As Variant

    ' --- EINSTELLUNGEN ---
    Set wsQuelle = ThisWorkbook.ActiveSheet ' Das aktuelle Blatt wird aufgeteilt
    spalteIndex = 1 ' IN WELCHER SPALTE STEHEN DIE WERTE? (A=1, B=2, C=3, ...)
    ' ---------------------

    Application.ScreenUpdating = False ' Verhindert Bildschirmflackern

    letzteZeile = wsQuelle.Cells(wsQuelle.Rows.Count, spalteIndex).End(xlUp).Row
    Set eindeutigeWerte = New Collection

    ' 1. Eindeutige Werte aus der Spalte sammeln
    On Error Resume Next
    For Each zelle In wsQuelle.Range(wsQuelle.Cells(2, spalteIndex), wsQuelle.Cells(letzteZeile, spalteIndex))
        If zelle.Value <> "" Then
            eindeutigeWerte.Add zelle.Value, CStr(zelle.Value)
        End If
    Next zelle
    On Error GoTo 0

    ' 2. Für jeden eindeutigen Wert ein Blatt erstellen/befüllen
    For Each wert In eindeutigeWerte
        ' Prüfen, ob Blatt bereits existiert, sonst erstellen
        On Error Resume Next
        Set wsZiel = Sheets(CStr(wert))
        On Error GoTo 0

        If wsZiel Is Nothing Then
            Set wsZiel = Sheets.Add(After:=Sheets(Sheets.Count))
            wsZiel.Name = CStr(wert)
        Else
            wsZiel.Cells.ClearContents ' Bestehende Daten löschen
        End If

        ' 3. Daten filtern und kopieren
        wsQuelle.UsedRange.AutoFilter Field:=spalteIndex, Criteria1:=wert
        wsQuelle.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=wsZiel.Range("A1")

        ' Spaltenbreite automatisch anpassen
        wsZiel.Columns.AutoFit

        ' Zielvariable zurücksetzen für den nächsten Durchlauf
        Set wsZiel = Nothing
    Next wert

    ' Filter entfernen und Abschluss
    wsQuelle.AutoFilterMode = False
    Application.ScreenUpdating = True

    MsgBox "Fertig! Die Daten wurden auf " & eindeutigeWerte.Count & " Blätter aufgeteilt.", vbInformation
End Sub

3. Schritt-für-Schritt-Anleitung zur Einrichtung

Schritt 1: Den VBA-Editor öffnen

Drücke die Tastenkombination ALT + F11, um den Visual Basic Editor zu öffnen.

Schritt 2: Ein neues Modul einfügen

Klicke im Menü oben auf Einfügen -> Modul. Ein weißes Textfenster erscheint.

Schritt 3: Code einfügen

Kopiere den obigen Code und füge ihn in das Fenster ein.

Schritt 4: Einstellungen anpassen

Im Code findest du den Bereich --- EINSTELLUNGEN ---.

  • Ändere die Zahl bei spalteIndex = 1, wenn deine Daten nach einer anderen Spalte als Spalte A aufgeteilt werden sollen (z.B. 2 für Spalte B).

Schritt 5: Das Makro ausführen

Es gibt zwei Möglichkeiten:

  1. Drücke direkt im VBA-Editor F5.
  2. Gehe zurück zu Excel, klicke auf Entwicklertools -> Makros, wähle SpalteAufteilenInBlaetter aus und klicke auf Ausführen.

4. Funktionsweise erklärt

  1. Eindeutige Werte finden: Das Makro durchläuft die gewählte Spalte und merkt sich jeden Wert (z.B. jeden Namen) genau einmal in einer "Collection".
  2. Blatt-Erstellung: Für jeden Namen in der Liste prüft das Makro, ob bereits ein Tabellenblatt mit diesem Namen existiert. Wenn nicht, wird eines erstellt.
  3. Autofilter: Das Makro nutzt die eingebaute Filterfunktion von Excel. Es filtert die Haupttabelle nach dem aktuellen Namen.
  4. Kopieren: Nur die sichtbaren Zeilen (die zum Namen passen) werden kopiert und in das jeweilige Blatt eingefügt.
  5. Aufräumen: Der Filter wird am Ende wieder deaktiviert, damit deine Originaldaten unverändert sichtbar bleiben.

5. Tipps & Fehlerbehebung

  • Überschriften: Das Makro geht davon aus, dass die erste Zeile deiner Tabelle Überschriften enthält.
  • Sonderzeichen: Die Werte in der Spalte werden als Blattnamen verwendet. Vermeide daher Sonderzeichen wie :, \, /, ?, *, [ oder ], da Excel diese in Blattnamen nicht erlaubt.
  • Datenschutz: Wenn du die Datei weitergibst, denke daran, dass die Makros nur in einer .xlsm-Datei gespeichert bleiben.
  • Bestehende Blätter: Falls ein Blatt bereits existiert, löscht das Makro im aktuellen Code den alten Inhalt, bevor es die neuen gefilterten Daten einfügt. Das verhindert Dubletten.
0