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, denOn Error Resume Nextabfä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. Mitwb.Worksheets.Countlandet 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.Addliefert 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:
| Position | Anweisung |
|---|---|
| als letztes Blatt | wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count)) |
| als erstes Blatt | wb.Worksheets.Add(Before:=wb.Sheets(1)) |
| hinter dem Blatt „Übersicht“ | wb.Worksheets.Add(After:=wb.Sheets("Übersicht")) |
| vor dem aktiven Blatt | wb.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.Addund danachActiveSheet.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.Unprotectauf. - 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.
