VBA Werte einfügen & Inhalte einfügen (englisch PasteSpecial)

Dieses Tutorial beschreibt wie Sie PasteSpecial in VBA verwenden, um Werte, Formate und mehr einzufügen.

 

Wenn Sie in Excel eine Zelle kopieren und einfügen, werden sämtliche Eigenschaften der Zelle kopiert und eingefügt: Werte, Formate, Formeln, Zahlenformatierungen, Rahmen usw:

Stattdessen können Sie die Funktion „Inhalte einfügen“ (englisch Paste Special) verwenden, um nur ausgewählte Zelleigenschaften einzufügen. In Excel erreichen Sie das Menü „Paste Special“ mit der Tastenkombination STRG + ALT + V, nachdem Zelleninhalte bereits in den Zwischenspeicher kopiert wurden:

Hierauf sind alle Kombinationen der Eigenschaften einer Zelle aufgelistet, die Sie zum Einfügen verwenden können.

Wenn Sie die Funktion „Inhalte einfügen“ während einer Makro-Aufzeichnung verwenden, erhalten Sie den Code dafür. Dies ist oft der einfachste Weg in VBA „Inhalte einfügen“ zu verwenden.

Werte einfügen

Werte einfügen fügt nur den „Wert“ der Zelle ein. Wenn die Zelle eine Formel enthält, fügt „Werte einfügen“ das Ergebnis der Formel ein.

Dieser Code kopiert und fügt Werte für eine einzelne Zelle auf demselben Arbeitsblatt ein:

Range("A1").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues

Werte kopieren und in ein anderes Blatt einfügen

Mit diesem Beispiel werden Werte für einzelne Zellen auf verschiedenen Arbeitsblättern kopiert und eingefügt

Sheets("Tabelle1").Range("A1").Copy
Sheets("Tabelle2").Range("B1").PasteSpecial Paste:=xlPasteValues

Mit diesen Beispielen können Sie Werte für einen Zellbereich kopieren und einfügen:

Bereiche kopieren und Werte einfügen

Range("A1:B3").Copy
Range("C1").PasteSpecial Paste:=xlPasteValues

Spalten kopieren und Werte einfügen

Columns("A").Copy
Columns("B").PasteSpecial Paste:=xlPasteValues

Zeilen kopieren und Werte einfügen

Rows(1).Copy
Rows(2).PasteSpecial Paste:=xlPasteValues

Einfügen von Werten und Zahlenformaten

Beim Einfügen von Werten wird nur der Wert einer Zelle eingefügt. Es werden keine Formatierungen eingefügt, auch keine Zahlenformatierung.

Wenn Sie Werte einfügen, werden Sie wahrscheinlich auch die Zahlenformatierung einfügen wollen, damit die Werte korrekt formatiert bleiben. Sehen wir uns dazu ein Beispiel an.

Hier fügen wir den Wert einer Zelle ein, die einen Prozentsatz enthält:

Sheets("Tabelle1").Columns("D").Copy
Sheets("Tabelle2").Columns("B").PasteSpecial Paste:=xlPasteValues

Beachten Sie, dass die Prozent-Formatierung verloren geht und stattdessen nur noch ein scheinbar willkürlicher Dezimalwert angezeigt wird.

Lassen Sie uns stattdessen die Option „Werte und Zahlen einfügen“ verwenden:

Sheets("Tabelle1").Columns("D").Copy
Sheets("Tabelle2").Columns("B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Wie Sie sehen, bleibt die Zahlenformatierung dieses Mal erhalten.

.Value anstelle von .Paste

Anstatt einer Einfügen-Funktion, können Sie auch die Wert-Eigenschaft „Value“ des Range-Objekts verwenden:

Damit wird der Wert der Zelle A2 gleich dem Wert der Zelle B2 gesetzt

Range("A2").Value = Range("B2").Value

Sie können auch einen ganzen Bereich von Zellen gleich dem Wert einer einzelnen Zelle setzen:

Range("A2:C5").Value = Range("A1").Value

oder einen Bereich von Zellen gleich einem anderen gleich großen Bereich von Zellen:

Range("B2:D4").Value = Range("A1:C3").Value

Die Value-Eigenschaft zu nutzen verursacht weniger Schreibarbeit. Außerdem sollten Sie, wenn Sie Excel VBA beherrschen wollen, mit der Value-Eigenschaft von Zellen umgehen können.

Value-Eigenschaft vs. Value2-Eigenschaft

Technisch gesehen ist es besser, die Value2-Eigenschaft einer Zelle zu verwenden. Value2 ist etwas schneller (bemerkbar erst bei extrem großen Berechnungen) und die Value-Eigenschaft könnte Ihnen ein abgeschnittenes Ergebnis liefern, wenn die Zelle als Währung oder als Datum formatiert ist. 99% der Codes, die ich gesehen habe, verwenden .Value und nicht .Value2.  Ich persönlich verwende .Value2 nicht, aber Sie sollten wissen, dass es existiert.

Range("A2").Value2 = Range("B2").Value2

Copy Paste Builder

Wir haben einen „Copy Paste Code Builder“ erstellt, der es einfach macht, VBA-Code zum Kopieren (oder Ausschneiden) und Einfügen von Zellen zu erzeugen. Der Builder ist Bestandteil unseres VBA-Add-Ins: AutoMacro (in Kürze auch auf deutsch).

AutoMacro (in Kürze auch auf deutsch) enthält auch viele andere Code-Generatoren, eine umfangreiche Code-Bibliothek und leistungsstarke Coding Tools.

Paste Special – Formate und Formeln

Neben dem Einfügen von Werten sind die gebräuchlichsten Paste-Special-Optionen das Einfügen von Formaten und das Einfügen von Formeln.

Formate einfügen

Mit Formate einfügen können Sie alle Zellformatierungen einfügen.

Range("A1:A10").Copy
Range("B1:B10").PasteSpecial Paste:=xlPasteFormats

Formeln einfügen

Mit Formeln einfügen werden ausschließlich die Formeln eingefügt. Dies ist sehr nützlich, wenn Sie Formeln bestimmter Zellen kopieren wollen, aber keine Zellhintergrundfarben (oder andere Zellformatierungen) mitkopieren möchten.

Range("A1:A10").Copy
Range("B1:B10").PasteSpecial Paste:=xlPasteFormulas

Formeln und Zahlenformate einfügen

Ähnlich wie beim Einfügen von Werten mit Zahlenformaten oben, können Sie auch Zahlenformate mit Formeln gemeinsam kopieren und einfügen.

Hier werden wir eine Zellformel mit Buchhaltungszahlenformatierung kopieren und nur Formeln einfügen.

Sheets("Tabelle1").Range("D3").Copy
Sheets("Tabelle2").Range("D3").PasteSpecial xlPasteFormulas

Beachten Sie, dass die Zahlenformatierung verloren geht und stattdessen ein nicht kaufmännisch gerundeter Wert angezeigt wird.

Lassen Sie uns stattdessen die Formeln und Zahlenformate mit einfügen:

Sheets("Tabelle1").Range("D3").Copy
Sheets("Tabelle2").Range("D3").PasteSpecial xlPasteFormulasAndNumberFormats

Wie Sie sehen, wird die Zahlenformatierung nun ebenfalls mit eingefügt, wobei das Buchhaltungsformat erhalten bleibt.

Einfügen Spezial – Transponieren und Leerzelle überspringen

Einfügen Spezial – Transponieren

Mit PasteSpecial Transpose können Sie Zellen kopieren und einfügen, indem Sie die Ausrichtung von oben-unten nach links-rechts (oder umgekehrt) ändern:

Sheets("Tabelle1").Range("A1:A5").Copy
Sheets("Tabelle1").Range("B1").PasteSpecial Transpose:=True

Einfügen Spezial – Leerzeichen überspringen

Leerzeichen überspringen (SkipBlanks) ist eine Spezial-Einfüge-Option, die nicht so oft verwendet wird, wie sie sollte.  Sie ermöglicht es beim Kopieren und Einfügen nur nicht-leere Zellen zu kopieren. Leere Zellen werden dabei nicht mitkopiert.

Im Beispiel unten wird in Spalte A kopiert. In Spalte B wird zunächst normal eingefügt und in Spalte C wird ein Einfüge-Vorgang mit der Option „SkipBlanks“ ausgeführt. Wie Sie sehen, bleiben die alten Werte in der Spalte C erhalten (Zeile 2 und 4), weil die entsprechenden Zellen in Spalte A leer sind und deswegen übersprungen werden.

Sheets("Tabelle1").Range("A1:A5").Copy
Sheets("Tabelle1").Range("B1").PasteSpecial SkipBlanks:=False
Sheets("Tabelle1").Range("C1").PasteSpecial SkipBlanks:=True

 

Andere Einfüge-Spezial-Optionen

Paste Special – Kommentare

Sheets("Tabelle1").Range("A1").Copy Sheets("Tabelle1").Range("E1").PasteSpecial xlPasteComments

Paste Special – Validierung

Sheets("Tabelle1").Range("A1:A4").Copy
Sheets("Tabelle1").Range("B1:B4").PasteSpecial xlPasteValidation

Paste Special – Alles mit Source Formatierung einfügen

Workbooks(1).Sheets("Tabelle1").Range("A1:A2").Copy
Workbooks(2).Sheets("Tabelle1").Range("A1").PasteSpecial
Workbooks(2).Sheets("Tabelle1").Range("B1").PasteSpecial xlPasteAllUsingSourceTheme

Paste Special – Alles außer Ränder

Range("B2:C3").Copy
Range("E2").PasteSpecial
Range("H2").PasteSpecial xlPasteAllExceptBorders

PasteSpecial – Spaltenbreiten

Ein persönlicher Favorit von mir. PasteSpecial Spaltenbreite kopiert und fügt unter Beibehaltung der Spaltenbreite  ein.

Range("A1:A2").Copy
Range("C1").PasteSpecial
Range("E1").PasteSpecial xlPasteColumnWidths

PasteSpecial – Alle bedingte Formatierungen vereinen

Range("A1:A4").Copy
Range("C1").PasteSpecial
Range("E1").PasteSpecial xlPasteAllMergingConditionalFormats