Neues Tabellenblatt per VBA anlegen und benennen

Für jeden Monat ein neues Auswertungsblatt, für jeden Import ein Blatt mit den Rohdaten, für jeden Kunden ein eigenes Blatt: Solche Aufgaben erledigt ein Makro schneller und ohne Tippfehler. Wichtig ist dabei, dass es nicht abbricht, wenn ein Blatt mit dem Namen schon existiert.

Die Kurzlösung

Die Funktion BlattHolen liefert das Blatt mit dem gewünschten Namen. Gibt es das Blatt noch nicht, legt sie es als letztes Blatt an:

Option Explicit

' Liefert das Tabellenblatt mit dem Namen blattName.
' Gibt es das Blatt noch nicht, wird es als letztes Blatt angelegt.
Public Function BlattHolen(ByVal wb As Workbook, ByVal blattName As String) As Worksheet
    Dim blatt As Object
    Dim ws As Worksheet
    Dim nummer As Long
    Dim beschreibung As String

    On Error Resume Next
    Set blatt = wb.Sheets(blattName)            ' Groß- und Kleinschreibung spielt keine Rolle
    On Error GoTo 0

    If Not blatt Is Nothing Then
        If TypeOf blatt Is Worksheet Then
            Set BlattHolen = blatt
            Exit Function
        End If
        Err.Raise vbObjectError + 1, , "Ein Diagrammblatt heißt bereits """ & blattName & """."
    End If

    Set ws = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    On Error GoTo Fehler
    ws.Name = blattName
    Set BlattHolen = ws
    Exit Function

Fehler:
    nummer = Err.Number
    beschreibung = Err.Description
    Application.DisplayAlerts = False
    ws.Delete                                   ' Blatt mit ungültigem Namen wieder entfernen
    Application.DisplayAlerts = True
    Err.Raise nummer, , "Ungültiger Blattname """ & blattName & """: " & beschreibung
End Function

' Beispiel: Blatt für den aktuellen Monat holen oder anlegen
Public Sub MonatsblattAnlegen()
    Dim ws As Worksheet
    Set ws = BlattHolen(ThisWorkbook, "Auswertung " & Format(Date, "yyyy-mm"))
    ws.Range("A1").Value = "Stand: " & Format(Now, "dd.mm.yyyy hh:nn")
End Sub

Fügen Sie den Code im VBA-Editor (Alt+F11) über Einfügen, Modul ein und speichern Sie die Datei als .xlsm.

So arbeitet der Code

  • Vorhandenes Blatt suchen: wb.Sheets(blattName) findet das Blatt direkt über seinen Namen, ohne Schleife. Gibt es keins, entsteht ein Fehler, den On Error Resume Next abfängt. Die Variable bleibt dann leer.
  • Sheets statt Worksheets: Sheets enthält auch Diagrammblätter. Nur so wird erkannt, dass ein Name schon von einem Diagrammblatt belegt ist.
  • Ans Ende setzen: After:=wb.Sheets(wb.Sheets.Count) setzt das neue Blatt hinter das letzte Blatt. Mit wb.Worksheets.Count landet es nicht am Ende, wenn hinten ein Diagrammblatt steht. Bei vier vorhandenen Blättern, davon das letzte ein Diagrammblatt, steht das neue Blatt dann an vierter Stelle vor dem Diagrammblatt.
  • Direkt benennen: Worksheets.Add liefert das neue Blatt zurück. So benennen Sie genau dieses Blatt und nicht irgendein gerade aktives.
  • Ungültige Namen: Ist der Name ungültig, etwa zu lang oder mit einem Schrägstrich, löscht der Code das eben angelegte leere Blatt wieder und meldet den Grund. Welche Namen erlaubt sind, steht im Beitrag Blattname aus Zelle.

An anderer Stelle einfügen

Die Position bestimmen die Argumente Before und After:

PositionAnweisung
als letztes Blattwb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
als erstes Blattwb.Worksheets.Add(Before:=wb.Sheets(1))
hinter dem Blatt „Übersicht“wb.Worksheets.Add(After:=wb.Sheets("Übersicht"))
vor dem aktiven Blattwb.Worksheets.Add (ohne Argument)

Blatt aus einer Vorlage

Soll das neue Blatt schon Überschriften, Formate und Formeln enthalten, kopieren Sie ein verstecktes Vorlageblatt:

Dim ws As Worksheet
ThisWorkbook.Worksheets("Vorlage").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set ws = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ws.Name = "Auswertung " & Format(Date, "yyyy-mm")
ws.Visible = xlSheetVisible

Copy liefert kein Objekt zurück, das kopierte Blatt ist danach das letzte Blatt. Ist die Vorlage ausgeblendet, ist es auch die Kopie, deshalb die letzte Zeile.

Typische Fehler

  • Doppelter Name: Ein Name darf nur einmal vorkommen, ohne Unterschied zwischen Groß- und Kleinschreibung. „AUSWERTUNG“ und „Auswertung“ gelten als gleich. Die Funktion oben gibt in diesem Fall das vorhandene Blatt zurück.
  • ActiveSheet nach dem Einfügen: Ältere Codes schreiben Sheets.Add und danach ActiveSheet.Name = …. Das funktioniert meist, bezieht sich aber auf die aktive Mappe. Läuft das Makro aus einer anderen Mappe heraus, benennt es dort ein Blatt um.
  • Arbeitsmappe geschützt: Bei geschützter Arbeitsmappenstruktur (Überprüfen, Arbeitsmappe schützen) lassen sich keine Blätter einfügen. Heben Sie den Schutz im Makro vorher mit wb.Unprotect auf.
  • Viele Blätter: Ein Blatt pro Tag oder Datensatz macht Mappen schnell unübersichtlich. Oft ist eine einzige Tabelle mit einer Datumsspalte und Filtern die bessere Lösung.

Passende Beiträge

Lieber machen lassen?

Wir programmieren die Lösung für Ihre Datei.

Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.