Mehrere Spalten kopieren VBA verbessern?

Forumthread: Mehrere Spalten kopieren VBA verbessern?

25.05.2016 15:15:53

Michael

Hallo zusammen,
durch folgenden Code werden Daten kopiert und eingefügt:

Sub Test03()
Workbooks("Original.xlsx").Sheets("Sheet1"). _
Range("D2:D6000,AM2:AM6000,AH2:AH6000,AC2:AC6000").Copy
ThisWorkbook.Sheets("Blatt1").Range("A2").PasteSpecial
End Sub

Funktioniert einwandfrei; Endpunkt Zelle 6000 ist willkürlich von mir gesetzt, da die Datenmenge sich monatlich ändern.
Am Anfang habe ich folgenden Code benutzt:

Sub test02()
With Sheets("Sheet1")
lz01 = .Range("D" & .Cells.Rows.Count).End(xlUp).Row
.Range("D2:D" & lz01).Copy
ThisWorkbook.Sheets("Blatt1").Range("A2").PasteSpecial
End With
End Sub

Hier sucht sich der Code selbstständig die letzte Zeile.
Ich schaffe es aber nicht diesen Code so zu verändern, dass er mehrere Spalten gleichzeitig kopiert.
Ich könnte jetzt natürlich diesen einfach kopieren und entsprechend ändern; geht das eventuell auch in einem Rutsch.
Vielen Dank schon einmal
Gruß
Michael

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Mehrere Spalten kopieren VBA verbessern?

25.05.2016 15:40:24

Michael
AW: Mehrere Spalten kopieren VBA verbessern?

25.05.2016 15:48:26

Daniel
AW: Mehrere Spalten kopieren VBA verbessern?

30.05.2016 11:56:18

Michael
Sehr seltsam

30.05.2016 17:54:59

Michael
AW: Sehr seltsam

31.05.2016 08:38:11

Michael

AW: Mehrere Spalten kopieren VBA verbessern?

25.05.2016 15:40:24

Michael

Hi Michael,
das ist nur ein String, den Du wie folgt zusammensetzt:

.Range("D2:D" & lz01 & ",AM2:AM" & lz01 & ",AC2:AC" & lz01).Copy

usw.
Schöne Grüße,
Michael

AW: Mehrere Spalten kopieren VBA verbessern?

25.05.2016 15:48:26

Daniel

Hi
zum Beispiel so:
Intersect(.Range("2:" & lz01), .Range("D:D,AM:AM,AH:AH,AC:AC")).Copy
oder
.Range("D2,AC2,AH2,AM2").Resize(lz01 - 1).Copy
Gruß Daniel

AW: Mehrere Spalten kopieren VBA verbessern?

30.05.2016 11:56:18

Michael

Hallo Michael, hallo Daniel,
aufgrund verlängertes Wochenende komme ich erst jetzt dazu mich bei euch für die Antworten zu bedanken.
Michael:
Danke, absolut perfekt. Habe es so übernommen.
Daniel:
Dein erstes Beispiel funktioniert; hat aber den Nachteil, dass er nicht in der Reihenfolge, wie er kopiert auch einfügt. Reihenfolge beim Kopieren: D, AM, AH, AC; Reihenfolge beim Einfügen: D, AC, AH, AM
Das zweite Beispiel bricht mit der Fehlermeldung: Laufzeitfehler 1004: Anwendungs- oder objektdefinierter Fehler ab.
Nochmals Danke
Gruß
Michael

Sehr seltsam

30.05.2016 17:54:59

Michael

Hi zusammen,
Daniel liebt Intersect, und das nicht ohne Grund.
Aber es scheint, daß das Kopieren mehrerer, nicht zusammenhängender Spalten mitunter zu seltsamen Effekten führt:

Option Explicit
Sub test()
Dim lz01&
lz01 = Range("D" & Cells.Rows.Count).End(xlUp).Row
Sheets(1).Range("D2:D" & lz01 & ",AM2:AM" & lz01 & _
",AH2:AH" & lz01 & ",AC2:AC" & lz01).Copy
Sheets(2).Range("A2").PasteSpecial
'kopiert FORMELN
Sheets(1).Range("D2:D" & lz01 & ",AM2:AM" & lz01 & _
",AH2:AH" & lz01 & ",AC2:AC" & lz01).Copy _
Sheets(2).Range("A2") ' man beachte den Unterstrich    ^
'kopiert WERTE, warum?
End Sub
Sub test2()
Dim lz01&, i&
Const von = "D,AM,AH,AC"
Const nach = "A,B,C,D"
Dim aVon, aNach
aVon = Split(von, ",")
aNach = Split(nach, ",")
lz01 = Range("D" & Cells.Rows.Count).End(xlUp).Row
For i = 0 To UBound(aVon)
Sheets(1).Range(aVon(i) & "2").Resize(lz01 - 1).Copy Sheets(2).Range(aNach(i) & "2")
Next
'kopiert FORMELn, wie erwartet
End Sub

Deshalb würde ich, um Unvorhersehbares zu vermeiden, immer die untere Variante wählen - durch die Definition der Spalten in den Konstanten ist das auch sehr leicht zu warten.
Ansonsten vielen Dank für die Rückmeldung und
schöne Grüße,
Michael

AW: Sehr seltsam

31.05.2016 08:38:11

Michael

Hallo Michael,
schönen Dank für die Erklärung.
Gruß
Michael

Infobox / Tutorial

Mehrere Spalten mit VBA kopieren und einfügen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um mehrere Spalten in Excel mithilfe von VBA zu kopieren, kannst du die folgende Schritt-für-Schritt-Anleitung befolgen:

Öffne den VBA-Editor: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Füge ein neues Modul hinzu: Klicke mit der rechten Maustaste auf VBAProject (deinWorkbookName) und wähle Einfügen > Modul.

Kopiere den folgenden Code in das Modul:

Sub MehrereSpaltenKopieren()
   Dim lz01 As Long
   lz01 = Sheets("Sheet1").Range("D" & Cells.Rows.Count).End(xlUp).Row

   Sheets("Sheet1").Range("D2:D" & lz01 & ",AM2:AM" & lz01 & ",AH2:AH" & lz01 & ",AC2:AC" & lz01).Copy
   Sheets("Blatt1").Range("A2").PasteSpecial
End Sub

Führe das Makro aus: Gehe zurück zu Excel, drücke ALT + F8, wähle MehrereSpaltenKopieren und klicke auf Ausführen.

Dieser Code sucht automatisch die letzte Zeile in Spalte D und kopiert dann die angegebenen Spalten in die Ziel-Tabelle.

Häufige Fehler und Lösungen

Laufzeitfehler 1004: Dieser Fehler tritt häufig auf, wenn eine der angegebenen Spalten nicht existiert oder die Ziel-Tabelle nicht korrekt referenziert wird. Überprüfe die Spaltenbezeichnungen und das Blatt, auf das du zugreifst.
Reihenfolge beim Einfügen: Wenn die Reihenfolge der kopierten Spalten nicht mit der Einfüge-Reihenfolge übereinstimmt, kannst du die Spalten manuell in der gewünschten Reihenfolge angeben. Verwende dazu Union oder passe die Zielzellen entsprechend an.

Alternative Methoden

Wenn du mehrere Spalten kopieren möchtest, gibt es verschiedene Ansätze:

Intersect-Methode:

Intersect(Sheets("Sheet1").Range("D:D,AM:AM,AH:AH,AC:AC"), Sheets("Sheet1").Range("2:" & lz01)).Copy

Resize-Methode:

Sheets("Sheet1").Range("D2,AC2,AH2,AM2").Resize(lz01 - 1).Copy

Beide Methoden bieten eine flexible Möglichkeit, mehrere Spalten auszuwählen und zu kopieren, aber achte darauf, dass die Reihenfolge beim Einfügen eventuell nicht übereinstimmt.

Praktische Beispiele

Hier sind einige praktische Beispiele, wie du mehrere Spalten kopieren kannst:

Kopieren mit Formeln:

Sheets(1).Range("D2:D" & lz01).Copy Sheets(2).Range("A2")

Kopieren von Werten:

Sheets(1).Range("D2:D" & lz01).Copy
Sheets(2).Range("A2").PasteSpecial xlPasteValues

Beachte, dass die Verwendung des Unterstrichs (_) im VBA-Code angezeigt wird, um Zeilen zu trennen, was die Lesbarkeit verbessert.

Tipps für Profis

Verwende Konstanten für Spaltennamen: Definiere spaltenbezogene Konstanten, um deinen Code übersichtlicher zu gestalten und Wartungsaufwand zu reduzieren.
```
Const von = "D,AM,AH,AC"
Const nach = "A,B,C,D"
```
Fehlerbehandlung einbauen: Nutze On Error Resume Next, um den Code robuster zu machen, insbesondere wenn du mit mehreren Spalten arbeitest, die möglicherweise nicht vorhanden sind.

FAQ: Häufige Fragen

1. Wie kann ich mehrere Spalten aus verschiedenen Blättern kopieren?
Du kannst einfach die Blattnamen in deinem Code anpassen, um Daten von einem Blatt in ein anderes zu kopieren.

2. Funktioniert das auch in älteren Excel-Versionen?
Ja, der bereitgestellte VBA-Code sollte in den meisten Excel-Versionen, die VBA unterstützen, funktionieren. Achte jedoch darauf, dass einige Funktionen je nach Version variieren können.

Beliebteste Forumthreads (12 Monate)

Mehrere Spalten kopieren VBA verbessern?

Forumthread: Mehrere Spalten kopieren VBA verbessern?

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Mehrere Spalten mit VBA kopieren und einfügen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread