Service: Excel-VBA: Zellinhalte kopieren

Grundlegendes zum Kopieren mit VBA

Es gibt zwei grundlegend unterschiedliche Arten per VBA, Zellen oder Zellbereiche zu kopieren:


Kopieren mit Hilfe der Windows-Zwischenablage

Hier immitiert VBA quasi eine Benutzer-Aktion, nämlich das 'Kopieren und Einfügen' mit Hilfe der Windows-Zwischenablage. Entsprechender Code sieht etwa so aus:

' Einen Bereich markieren
    Range("A1:A2").Select
' Bereich in Zwischenablage kopieren
    Selection.Copy
' Ein anderes Arbeitsblatt aktivieren
    ActiveSheet.Next.Select
' Dort einen Bereich aktivieren
    Range("C3").Select
' Inhalt der Zwischenablage kopieren
    ActiveSheet.Paste

Bei dieser Methode werden nicht nur die Zellinhalte kopiert, sondern auch sämtliche Formatierungseinstellungen (wie Schrift-Eigenschaften, Rahmen- und Hintergrund-Einstellungen, etc).

Eine etwas kürzere VBA-Variante, die das gleiche Ziel erreicht:

' Bereich in Zwischenablage kopieren
    Range("A1:A2").Copy
' Ein anderes Arbeitsblatt aktivieren
    Application.Goto ActiveSheet.Next.Range("C3")
' Inhalt der Zwischenablage kopieren
    ActiveSheet.Paste

Seitenanfang


Direktes Kopieren von Zellinhalten

Beim direkten Kopieren wird der Inhalt einzelner Zellen oder Zellbereiche unmittelbar (d.h. ohne Zwischenablage) direkt von einer Zelle in eine andere kopiert (bzw. von einem Zellbereich in einen anderen kopiert).

Ein Merkmal dieser Methode: Formatierungseinstellungen werden nicht mitkopiert. Das kann je nach Situation ein Vor- oder Nachteil sein.

Ein anderes Merkmal: Im VBA Code ist es nicht erforderlich, Arbeitsblätter oder Zellbereiche zu aktivieren. Statt dessen arbeitet man mit direkten Zuweisungen.

Beim direkten Kopieren sind unendlich viele Varianten denkbar. Ich möchte hier versuchen, das grundlegende Prinzip anhand einiger Beispiele zu verdeutlichen.

Einzelne Zelle kopieren

Das wohl einfachste aller denkbaren Beispiele:

' Der Inhalt von Zelle 'A1' des aktuellen Arbeitsblatts
' wird der Zelle 'B2' des aktuellen Arbeitsblatts zugewiesen
    Cells("B2") = Cells("A1")

Wohlgemerkt: Der Inhalt wird kopiert! In 'B2' steht also hinterher kein Verweis auf 'A1' sondern genau der gleiche Inhalt wie in 'A1'!

Davon sind nun unendlich viele Varianten denkbar, bei denen z.B. über Arbeitsblätter hinweg kopiert wird

    ' Von Tabelle2!A1 nach A1 in aktueller Tabelle
        Cells("A1") = Sheets("Tabelle2").Cells("A1")
    ' Von A1 in aktueller Tabelle nach Tabelle2!A1
        Sheets("Tabelle2").Cells("A1") = Cells("A1")
    ' Von Tabelle1!A1 nach Tabelle2!A1
        Sheets("Tabelle2").Cells("A1") = Sheets("Tabelle1").Cells("A1")

Seitenanfang


Inhalt markierter Zellen kopieren

Angenommen, Sie haben zwei Tabellen in einer Arbeitsmappe. Tabelle "Quelle" enthält einige Zellen, die markiert sind und deren Inhalt Sie in Tabelle "Ziel" an die gleiche Stelle kopieren möchten.

'---------------------------------------------------------------------------
' Procedure.: CopySelectedCells
' Author....: Ralf Kunsmann - http://www.kunsmann.de
' Date......: 2007 07 30
' Purpose...: Einen Zellbereich aus einer Tabelle in eine andere kopieren.
' ..........: Dieser Code funktioniert, wenn die aktuelle (geöffnete) Excel-
' ..........: Datei eine Tabelle namens 'Ziel' beinhaltet.
'---------------------------------------------------------------------------
Public Sub CopySelectedCells()

' Für schnelleren Zugriff innerhalb der folgenden 'For-Next'-Schleife:
' Referenz auf Ziel-Tabelle erstellen
    Dim wsZiel As Worksheet
    Set wsZiel = Sheets.Item("Ziel")

' 'For Each CellSelected In Selection ... Next' bedeutet:
' Mit jeder ausgewählten Zelle, den Code innerhalb 'For ... Next' ausführen
    Dim CellSelected As Range
    For Each CellSelected In Selection
    ' Kopiere den Inhalt der aktuellen Zelle (CellSelected) der Quell-Tabelle
    ' an die gleiche Stelle in der Zieltabelle
        wsZiel.Cells(CellSelected.Row, CellSelected.Column) = CellSelected
    Next

End Sub

Die Funktion 'CopySelectedCells()' kann man allgemeiner schreiben:

'---------------------------------------------------------------------------
' Procedure.: CopySelectedCells
' Author....: Ralf Kunsmann - http://www.kunsmann.de
' Date......: 2007 07 30
' Purpose...: Einen Zellbereich aus einer Tabelle in eine andere Kopieren.
'---------------------------------------------------------------------------
Public Sub CopySelectedCells(wsZiel as Worksheet)

' 'For Each CellSelected In Selection ... Next' bedeutet:
' Mit jeder ausgewählten Zelle, den Code innerhalb 'For ... Next' ausführen
    Dim CellSelected As Range
    For Each CellSelected In Selection
    ' Kopiere den Inhalt der aktuellen Zelle (CellSelected) der Quell-Tabelle
    ' an die gleiche Stelle in der Zieltabelle
        wsZiel.Cells(CellSelected.Row, CellSelected.Column) = CellSelected
    Next

End Sub

Man kann 'CopySelectedCells()' dann für beliebige Zieltabellen verwenden. Etwa so:

    CopySelectedCells Sheets.Item("Ziel")

oder so:

' Eine (andere) Exceldatei öffnen
    Dim wbZiel as WorkBook
    Set wbZiel = OpenWorkBook "IrgendeineExcelDatei.xls"

' Daten aus aktueller Tabelle in Tabelle 'Ziel' der
' soeben geöffneten Datei kopieren
    CopySelectedCells wbZiel.Sheets.Item("Ziel")

' Wichtig: Datei wieder schließen!
    wbZiel.Close

Seitenanfang


Einen beliebigen Block von Zellen kopieren

Immer wieder wird in Excel-Foren die Frage gestellt, wie man einen Bereich von Zellen aus einem Arbeitsblatt in ein anderes kopieren kann. Die naheliegenste Variant ist folgende:

Range("B1:B6").Value = Range("A1:A6").Value

Diese Variante tut jedoch unter Umständen nicht, was erwartet wird. Grund: Wenn Zellen z.B.: Berechnungsformeln, Zellverweise oder eine Kombination daraus enthalten, gehen diese Informationen durch das Kopieren verloren.

Auf der anderen Seite wird die folgende Anweisung nichts kopieren:

Range("B1:B6") = Range("A1:A6") ' Kopiert _nichts_!

Das ist der Grund dafür, dass alle Beispiele auf dieser Seite mit einzelnen Zellen operieren und dabei auf Angabe der Eigenschaft 'Value' bewusst verzichtet wird.

Das hat zur Folge, dass Excel beim Kopieren flexibel agiert und z.B. Formeln statt der Berechnungsergebnisse von Formeln kopiert. Wie in folgenden Beispielen:

' Referenz auf Zieltabelle erstellen
    Dim wsTarget as WorkSheet
    Set wsTarget = Sheets.Item("Ziel") ' Tabelle 'Ziel', aktuelle Datei

' Referenz auf Quelltabelle erstellen
    Dim wsSource as WorkSheet
    Set wsSource = Sheets.Item("Quelle") ' Tabelle 'Quelle', aktuelle Datei

' Die ersten 10 Zeilen und Spalten aus Tabelle 'Quelle'
' in Tabelle 'Ziel' kopieren
    Dim cRow As Long, cCol As Long
    For cRow = 1 To 10
        For cCol = 1 To 10
            wsTarget.Cells(cRow, cCol) = wsSource.Cells(cRow, cCol)
        Next
    Next

Der obenstehende Code hat allerdings den Nachteil, dass er sehr unflexibel ist, da die Bezeichnungen der Tabellen und der Zellbereiche fest einprogrammiert sind.

Die Alternative ist eine generische Kopierfunktion, die man je nach Bedarf mit unterschiedlichen Parametern aufrufen kann.

'---------------------------------------------------------------------------
' Procedure.: CopyCells
' Author....: Ralf Kunsmann
' Date......: 2007 07 20
' Purpose...: Einen Zellbereich aus einem Arbeitsblatt einer Tabelle in
'             ein anderes Arbeitsblatt einer (anderen) Tabelle kopieren.
'---------------------------------------------------------------------------
Public Sub CopyCells(StartRowSourceTable As Long, StartColSourceTable As Long, _
                     StartRowTargetTable As Long, StartColTargetTable As Long, _
                     NoOfRows As Long, NoOfCols As Long, _
                     SourceTable As Worksheet, TargetTable As Worksheet)

    On Error GoTo ErrorCopyCells

    Dim cRowSourceTable As Long
    cRowSourceTable = StartRowSourceTable
    Dim cRowTargetTable As Long
    cRowTargetTable = StartRowTargetTable

    Dim cColSourceTable As Long
    Dim cColTargetTable As Long

    Dim cRow As Long, cCol As Long
    For cRow = 1 To NoOfRows
        cColSourceTable = StartColSourceTable
        cColTargetTable = StartColTargetTable
        For cCol = 1 To NoOfCols
            TargetTable.Cells(cRowTargetTable, cColTargetTable) = _
                SourceTable.Cells(cRowSourceTable, cColSourceTable)
            cColSourceTable = cColSourceTable + 1
            cColTargetTable = cColTargetTable + 1
        Next
        cRowSourceTable = cRowSourceTable + 1
        cRowTargetTable = cRowTargetTable + 1
    Next

    Exit Sub

ErrorCopyCells:
    Dim sMsg As String
    sMsg = "Kopieren fehlgeschlagen. Grund: " & _
           Err.Description & " (Nr.: " & Err.Number & ")"
    MsgBox sMsg, vbInformation, "Zellbereich kopieren"
End Sub

Aufrufbeispiel für CopyCells():

' Kopiert Zeile 1 bis 5, Spalte 1, 2 aus Arbeitsblatt 'Quelle'
' in Zeile 10 bis 15, Spalte 3 bis 5 in Arbeitsblatt 'Ziel'.
    CopyCells 1, 1, 10, 3, 5, 2, _
              Sheets.Item("Quelle"), Sheets.Item("Ziel")

' Wie vor, jedoch befindet sich die Tabelle 'Ziel' in einer anderen Datei
    Dim wbZiel as WorkBook
    Set wbZiel = OpenWorkBook "EineAndereExcelDatei.xls"
    CopyCells 1, 1, 10, 3, 5, 2, _
              Sheets.Item("Quelle"), wbZiel.Sheets.Item("Ziel")
    wbZiel.Close

Hinweis: Statt "EineAndereExcelDatei.xls" muss der vollständige Pfad (also Laufwerk, Verzeichnis und Dateiname) der Zieldatei angegeben werden.

Seitenanfang


Die Erlaubnis, den Quellcode zeitlich, räumlich und inhaltlich unbegrenzt zu verwenden wird hiermit erteilt unter der Auflage, dass die Nennung von Ralf Kunsmann als Autor unter Angabe der Web-Adresse www.kunsmann.de im Quellcode erfolgt.


Ergebnis: vollständig vordefinierte Routine mit Fehlerbehandlung Interessant für Sie:

VBA-Extentions-Tools

Produktivität in VBA steigern
Routinearbeiten in VBA vereinfachen



Seitenanfang

Kontaktaufnahme- und Terminvereinbarung:

Bei Fragen und für Terminvereinbarungen erreichen Sie uns unter:

0 63 49 99 07 38

0 151 51 95 34 00

Oder nutzen Sie das Kontaktformular




Ihr Ansprechpartner:


Hier sollte das Fahnungsfoto zu sehen sein.

Ralf Kunsmann

Spezialist für VBA-Programmierung
(alle Office-Anwendungen)
Entwickler der
VBA-Extension-Tools