Hinzufügen eines Blattes zu einer Arbeitsmappe mit VBA

Letzte Aktualisierung am 20. Juli 2020 um 02:24 Uhr von Aoife McCambridge.

In diesem Artikel zeigen wir Ihnen, wie Sie mit VBA ein Blatt zu einer Excel-Arbeitsmappe hinzufügen. Hierzu werden im Folgenden zwei verschiedene Methoden beschrieben.


Hinzufügen eines Blattes zu einer Arbeitsmappe mit VBA

Abhängig vom gewünschten Ergebnis können Sie entweder Folgendes verwenden:

Kopiermethode => Für eine Kopie eines vorhandenen Blattes

Methode hinzufügen => So fügen Sie Ihrer Arbeitsmappe ein neues leeres Blatt hinzu.

Sie müssen auch sicherstellen, dass der Name des neuen Blattes kompatibel ist. Das heißt, der Name des Blattes sollte nicht mit einem vorhandenen identisch sein oder verbotene Zeichen enthalten. In beiden Fällen sollten die Überprüfungsmethoden gleich sein.

Kopiermethode

Kopieren Sie ein Blatt. Die Daten und das Format des neuen Blattes sind identisch mit dem Quellblatt.

Syntax

Ausdruck .Kopieren(Vorher Nachher)

Der Ausdruck kann entweder ein Blatt- oder ein Arbeitsblattobjekt sein.

Die Parameter Vorher und Nachher sind optional. Sie können nur einen dieser beiden Parameter angeben. Sie sind vom Variantentyp und können entweder die Blattposition vor oder nach der zu kopierenden darstellen. Wenn der Parameter weggelassen wird, wird das Blatt automatisch in eine neue Arbeitsmappe kopiert.

Was passiert dann?

Beim Erstellen eines neuen Blattes mit der Kopiermethode: Das Blatt wird an der gewünschten Stelle (vor oder nach einem anderen Blatt in der Arbeitsmappe) erstellt. Es wird basierend auf dem Namen des Quellblatts umbenannt. Beispiel: Sheet1 => Sheet1 (2) Es ist identisch mit dem kopierten Blatt. Es wird das aktive Blatt. Wenn das Blatt in eine neue Arbeitsmappe kopiert wird, wird es zur aktiven Arbeitsmappe.

Anwendungsbeispiele

Kopieren Sie das “Sheet1” nach “Sheet3”

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

Kopieren Sie das “Sheet4” vor das “Sheet2”:

Sheets("Sheet4").Copy Before:=Sheets("Sheet2")

Kopieren Sie das “Recap” -Blatt in eine neue Arbeitsmappe

Worksheets("recap").Copy

Kopieren Sie das als 1 indizierte Blatt an der letzten Position der Arbeitsmappe, unabhängig vom Namen des letzten Blattes:

Sheets(1).Copy After:=Sheets(Sheets.Count)

Kopieren Sie das am Anfang der Arbeitsmappe als 10 indizierte Blatt, unabhängig vom Namen des letzten Blattes:

Sheets(10).Copy Before:=Sheets(1)

Sie möchten mehrere Blätter Ihrer aktiven Arbeitsmappe in eine neue Arbeitsmappe kopieren:

Sheets(Array("Sheet1", "Sheet3", "Sheet5")).Copy

Die Add-Methode

Erstellt eine neue Tabelle (Grafiken oder Makros). Das neue Arbeitsblatt wird zum aktiven Blatt.

Dieses neue Blatt enthält keine Daten oder Formatierungen.

Syntax

Ausdruck .Hinzufügen(Vorher, Nachher, Zählen, Typ)

Der Ausdruck kann entweder ein Blatt- oder ein Arbeitsblattobjekt sein.

Die Parameter Vorher, Nachher, Anzahl und Typ sind optional.

Entweder und vorher – Sie können nur einen dieser Parameter angeben. Sie sind vom Variantentyp und können entweder die Blattposition vor oder nach der zu kopierenden darstellen.

Der Count-Parameter ist ebenfalls eine Variante. Es gibt die Anzahl der Blätter an, die Sie hinzufügen möchten.

Der Parameter Typ repräsentiert den Blatttyp, den Sie hinzufügen möchten. xlWorkSheet – fügt ein Arbeitsblatt hinzu xlChart – fügt ein Diagrammblatt hinzu xlExcel4MacroSheet – fügt ein Makroblatt hinzu (Excel4) xlExcel4IntlMacroSheet – fügt ein Makroblatt hinzu xlDialogSheet – fügt ein Dialogblatt hinzu.

NB: Vergessen Sie nicht, Ihre Parameter umzubenennen, da sonst die folgende Syntax den folgenden Fehler zurückgibt:

ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count), , 1, xlChart

Anwendungsbeispiele

Fügen Sie ein Blatt nach dem letzten Blatt in die Arbeitsmappe ein:

Sheets.Add After:=Worksheets(Worksheets.Count)

Fügt drei Blätter an der “ersten Position” der aktiven Arbeitsmappe hinzu:

 ActiveWorkbook.Sheets.Add Before:=Worksheets(1), Count:=3

Fügt ein Arbeitsblatt in einer Arbeitsmappe mit dem Namen “Wbk18” (Öffnen!) Nach dem Blatt mit dem Namen “Sheet4” hinzu.

WorkBooks("Wbk18").Sheets.Add After:=Worksheets("Sheet4"), Type:=xlChart

Testen des Blattes

Überprüfen Sie, ob das Blatt bereits in der Arbeitsmappe vorhanden ist

'Test si la feuille existe déjà
Function Feuil_Exist(strWbk As String, strWsh As String) As Boolean

'Gestionnaire d'erreur
On Error Resume Next
    '"Test"
    Feuil_Exist = (Workbooks(strWbk).Sheets(strWsh).Name = strWsh)
End Function

Überprüfen Sie, ob der Dateiname verbotene Zeichen enthält

'Test si la chaine contient un caractère à éviter
Function Valid_Name(strName As String, strChr As String) As Boolean
Dim i As Byte, Tb_Car() As String, strProhib As String

strProhib = "/:*?""<>|" ' Liste des caractères à éviter
Tb_Car = Split(StrConv(strProhib, vbUnicode), Chr$(0))
'Boucle sur tous les caractères à éviter
 'Nota : le -1 est dû au Split de la chaine par le séparateur Chr(0)
 'En effet, la chaine se terminant par un Chr(0) il convient d'exclure ce dernier caractère
For i = LBound(Tb_Car) To UBound(Tb_Car) - 1
    'Test si la chaîne contient un caractère prohibé
    If InStr(strName, Tb_Car (i)) > 0 Then
 'Si oui : Return False
        Valid_Name = False
 'ET Retourne le caractère prohibé
        strChr = Tb_Car(i)
        Exit Function
    End If
Next i
'Si OK : Return True
Valid_Name = True
End Function

Aufruf der Verifizierungsfunktion

Der Code ist für die Copy- oder Add-Methode gleich.

Sub Principale()
Dim strNewName As String, strCara As String

strNewName = "NewSheet"
If Valid_Name(strNewName, strCara) = False Then
    MsgBox "Le nom : " & strNewName & " est invalide." & vbCrLf & _
            "Un nom de feuille ne peut pas contenir le caractère : " & strCara, vbCritical
    Exit Sub
End If
If Feuil_Exist(ThisWorkbook.Name, strNewName) = True Then
    MsgBox "Le nom : " & strNewName & " est invalide." & vbCrLf & _
            "Ce nom de feuille est déjà utilisé dans ce classeur.", vbCritical
    Exit Sub
End If
ThisWorkbook.Sheets.Add 'Ou : ThisWorkbook.Sheets("Feuil1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = strNewName
End Sub

Einige nützliche Tipps für Office 2013

Office 2013 bietet zwei komplementäre Methoden zu den zuvor beschriebenen: Kopieren eines Zellenbereichs in ein Blatt und mehrere Blätter Die Add2-Methode für Sammlungen von Diagrammobjekten.

Kopieren Sie einen Zellenbereich mit der FillAcrossSheets-Methode

Diese Methode ist nur für Office 2013 verfügbar. Sie möchten einen Zellbereich von “Sheet1” nach “Sheet3”, “Sheet5” und “Sheet7” kopieren.

Sheets = Array("Sheet3", "Sheet5", "Sheet7")
Sheets(Sheets).FillAcrossSheets Worksheets("Sheet1").Range("A1:C5")

Die Parameter dieser Methode sind: Bereich: Erforderlich Typ: Optional xlFillWithAll: Kopieren Sie den Inhalt und die Formate. xlFillWithContents: Kopieren Sie den Inhalt. xlFillWithFormats: Kopierformate.

Die Add2-Methode

Diese Methode ist nur für Office 2013 verfügbar. Die Methode gilt für Sammlungen von Diagrammobjekten und gibt ein “Laufzeit“Fehler bei Verwendung mit Sheets- und WorkSheets-Objekten. Keine weiteren Informationen auf Microsoft.com außer der Syntax:

Ausdruck .Hinzufügen(Vorher, Nachher, Zählen, NewLayout)

Ausdruck repräsentiert ein Arbeitsblattobjekt.

Die Parameter sind identisch mit der Add-Methode. Ersetzen Sie einfach den Type-Parameter durch NewLayout. Wenn NewLayout auf True gesetzt ist, wird die Grafik nach den neuen Regeln eingefügt.