VBA Arbeitsblatt kopieren

In diesem Tutorial erfahren Sie, wie Sie ein Blatt oder Arbeitsblatt mit VBA kopieren.

Arbeitsblätter in neue Arbeitsmappen kopieren

Um ein Arbeitsblatt in eine neue Arbeitsmappe zu kopieren:

Sheets("Tabelle1").Copy

Das ActiveSheet in eine neue Arbeitsmappe kopieren

Um das ActiveSheet in eine neue Arbeitsmappe zu kopieren:

ActiveSheet.Copy

Mehrere Blätter in eine neue Arbeitsmappe kopieren

Um mehrere Blätter in eine neue Arbeitsmappe zu kopieren:

ActiveWindow.SelectedSheets.Copy

Ein Blatt innerhalb einer Arbeitsmappe kopieren

Zu Beginn haben wir Ihnen das einfachste Beispiel für das Kopieren von Blättern gezeigt: das Kopieren von Blättern in eine neue Arbeitsmappe.  Die folgenden Beispiele demonstrieren, wie ein Blatt innerhalb der gleichen Arbeitsmappe kopiert wird. Wenn ein Blatt innerhalb einer Arbeitsmappe kopiert wird, müssen Sie die Position des neuen Arbeitsblattes angeben.  Um eine Position anzugeben, weisen Sie VBA an, das Arbeitsblatt vor (Before) oder nach (After) einem anderen Arbeitsblatt zu verschieben.

Blatt vor einem anderen Blatt kopieren

Hier geben wir an, dass das Blatt vor Tabelle2 kopiert und eingefügt werden soll

Sheets("Tabelle1").Copy Before:=Sheets("Tabelle2")

Blatt vor dem ersten Blatt kopieren

Anstatt einen Blattnamen anzugeben, können Sie auch die Blattposition angeben. Hier wird ein Blatt vor dem ersten Blatt in der Arbeitsmappe kopiert und eingefügt.

Sheets("Tabelle1").Copy Before:=Sheets(1)

Nach Erstellung der Kopie wird das neue Blatt stattdessen das erste Blatt der Arbeitsmappe sein.

Blatt nach dem letzten Blatt kopieren

Verwenden Sie die Eigenschaft After, um VBA anzuweisen, das Blatt hinter einem anderen Blatt anzufügen. Im Folgenden wird ein Blatt nach dem letzten Blatt in der Arbeitsmappe kopiert und eingefügt:

Sheets("Tabelle1").Copy After:=Sheets(Sheets.Count)

Beachten Sie, dass wir Sheets.Count verwenden, um die Gesamtanzahl der Blätter in der Arbeitsmappe zu bestimmen und auf die letzte Blattposition zu verweisen.

Blätter verschieben

Sie können mit einer ähnlichen Syntax auch eine Verschiebung eines Blatt innerhalb seiner Arbeitsmappe bewirken. Mit diesem Code wird „Tabelle1“ an das Ende der Arbeitsmappe verschoben:

Sheets("Tabelle1").Move After:=Sheets(Sheets.Count)

Blatt kopieren und benennen

Nach dem Kopieren und Einfügen eines Blattes wird das neu erstellte Blatt zum ActiveSheet. Um also unser neues Blatt umzubenennen, sprechen Sie es einfach mit ActiveSheet.Name an:

Sub KopiertesBlattUmbenennen1()

Sheets("Tabelle1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "LetztesBlatt"

End Sub

Wenn der Blattname bereits existiert, wird der obige Code einen Fehler erzeugen.  Stattdessen können wir „On Error Resume Next“ verwenden, um VBA anzuweisen, die Änderung der Blattbezeichnung im Fehlerfall zu überspringen und mit dem Rest der Prozedur fortzufahren:

Sub KopiertesBlattUmbenennen2()

    Sheets("Tabelle1").Copy After:=Sheets(Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = "LetztesBlatt"
    On Error GoTo 0

End Sub

Oder verwenden Sie unsere Funktion BereichExistiert, um zu prüfen, ob der Blattname bereits existiert, bevor Sie versuchen, das Blatt zu kopieren:

Sub KopiertesBlattUmbenennen3()

    If BereichExistiert("LetztesBlatt") Then
        MsgBox "Blatt existiert bereits."
    Else
        Sheets("Tabelle1").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "LetztesBlatt"
    End If

End Sub

Function BereichExistiert(EinBlatt As String, Optional ByVal EinBereich As String = "A1") As Boolean
    Dim Test As Range
    On Error Resume Next
    Set Test = ActiveWorkbook.Sheets(EinBlatt).Range(EinBereich)
    RangeExists = Err.Number = 0
    On Error GoTo 0
End Function

Blätter kopieren und benennen anhand des Wertes einer Zelle

Möglicherweise möchten Sie ein Blatt basierend auf einem Zellwert kopieren und benennen.  Dieser Code benennt das Arbeitsblatt anhand des Zellwerts in A1

Sub BlattKopierenDurchZelleUmbenennen()

    Sheets("Tabelle1").Copy After:=Sheets(Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = Range("A1").Value
    On Error GoTo 0

End Sub

Arbeitsblatt in eine andere Arbeitsmappe kopieren

Bislang haben wir uns mit dem Kopieren von Arbeitsblättern innerhalb einer Arbeitsmappe beschäftigt. Es folgen Beispiele für das Kopieren und Einfügen von Arbeitsblättern in andere Arbeitsmappen. Mit diesem Code wird ein Arbeitsblatt als erstes Arbeitsblatt in eine anderen Arbeitsmappe kopiert:

Sheets("Tabelle1").Copy Before:=Workbooks("Beispiel.xlsm").Sheets(1)

So wird das Arbeitsblatt an das Ende der anderen Arbeitsmappe kopiert.

Sheets("Tabelle1").Copy After:=Workbooks("Beispiel.xlsm").Sheets(Workbooks("Beispiel.xlsm").Sheets.Count)

Beachten Sie, dass wir 1 durch Workbooks(„Beispiel.xlsm“).Sheets.Count ersetzt haben, um das letzte Arbeitsblatt zu ermitteln.

Arbeitsblatt in eine geschlossene Arbeitsmappe kopieren

Vielleicht möchten Sie ein Arbeitsblatt in eine ungeöffnete Arbeitsmappe kopieren.  Mit diesem Code wird eine geschlossene Arbeitsmappe geöffnet, so dass das Arbeitsblatt dort hinein kopiert werden kann.

Sub BlattInGeschlosseneMappeKopieren()
Application.ScreenUpdating = False

    Set GeschlosseneMappe = Workbooks.Open("D:\Dropbox\excel\artikel\beispiel.xlsm")
    Sheets("Tabelle1").Copy Before:=GeschlosseneMappe.Sheets(1)
    GeschlosseneMappe.Close SaveChanges:=True

Application.ScreenUpdating = True
End Sub

Blatt aus einer ungeöffneten Arbeitsmappe kopieren

Umgekehrt wird mit diesem Code ein Arbeitsblatt aus einer ungeöffneten Arbeitsmappe kopiert. Der Code übernimmt das Öffnen der Datei, sodass dieser manuelle Schritt für den Benutzer entfällt.

Sub BlattAusGeschlossenerMappeKopieren()
Application.ScreenUpdating = False

    Set GeschlosseneMappe = Workbooks.Open("D:\Dropbox\excel\artikel\beispiel.xlsm")
    GeschlosseneMappe.Sheets("Tabelle1").Copy Before:=ThisWorkbook.Sheets(1)
    GeschlosseneMappe.Close SaveChanges:=False

Application.ScreenUpdating = True
End Sub

Beachten Sie, dass wir in beiden Beispielen ScreenUpdating deaktiviert haben, damit der Prozess im Hintergrund durchlaufen wird. Dem Nutzer bleiben die Öffnungs- und Schließvorgänge anderer Arbeitsmappen verborgen. Dies erhöht die User-Experience.

Excel-Blatt mehrfach duplizieren

Sie können ein Excel-Blatt auch mehrfach duplizieren, indem Sie eine Schleife verwenden.

Sub BlattMehrfachKopieren()
Dim n As Integer
Dim i As Integer
On Error Resume Next

    n = InputBox("Wie viele Kopien möchten Sie erstellen?")

    If n > 0 Then
        For i = 1 To n
            ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
        Next
    End If

End Sub