Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1500to1504
1500to1504
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA: zwei Sheets vergleichen und Daten kopieren

Forumthread: VBA: zwei Sheets vergleichen und Daten kopieren

VBA: zwei Sheets vergleichen und Daten kopieren
05.07.2016 10:39:43
jam
Hallo zusammen,
ich hoffe, ihr könnt mir helfen.
Ich benötige für folgendes Vorhaben eine vba Lösung:
Es gibt in einer Datei zwei Tabellenblätter ( "Altdaten" und "Neudaten"), die ich gerne vergleichen möchte, dabei sollen die neuen Datensätze aus "Neudaten", die es noch nicht gibt ins Blatt "Altdaten" kopiert werden.
Für den Vergleich wird die Spalte B genommen ("Inventarnummer"). Gibt es also die Inventarnummer schon im Blatt "Altdaten" darf der ganze Datensatz nicht editiert bzw. kopiert werden. Gibt es die Inventarnr noch nicht, dann soll der komplette Datensatz ( bestehend aus Spalte A bis L ) ins Blatt "Neudaten" kopiert werden.
Als Bonbon wäre es schön, wenn die neuen Datensätze im Blatt "Altdaten" mit dem Vermerk "Neu" gekennzeichnet werden
( in Spalte M).
Vielen Dank für eure Unterstützung. Ich wäre für jeden Lösungsansatz wirklich sehr dankbar.
Gruß

Anzeige

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA: zwei Sheets vergleichen und Daten kopieren
05.07.2016 12:10:45
Werner
Hallo,
probier mal das. Aber bitte mit einer Kopie deiner Arbeitdatei, ich könnte nicht gegen, habe kurz mit dem Tablet geschrieben.
Sub Pruefen ()
Dim loLetzte As Long
Dim loLetzte1 As Long
Dim i As Long
Dim j As Long
Dim z As Long
loLetzte = Sheets("Altdaden").Cells(RowsCount, 2).End(xlUp).Row
loLetzte1 = Sheets("Neudaten").Cells(Rows.Count, 2).End(xlUp).Row
z = loLetzte + 1
For i = 2 To loLetzte
For j = 2 To loLetzte1
If Sheets("Altdaden").Cells(i, 2) = Sheets("Neudaten").Cells(j, 2) Then
With Sheets("Neudaten")
.Range(.Cells(j, 1), .Cells(j, 12)).Copy Sheets("Altdaden").Cells(z, 1)
Sheets("Altdaden").Cells(z, 13) = "Neu"
End With
z = z + 1
End If
Next j
Next i
End Sub
Gruß Werner

Anzeige
AW: VBA: zwei Sheets vergleichen und Daten kopieren
05.07.2016 13:04:46
jam
Hallo Werner,
vielen Dank für deine Hilfe. Ich bekomme leider einen "Laufzeitfehler 1004 - Anwendungs oder objektdefinierter Fehler".
Die fett markierte Zeile wird im debug Modus gelb angezeigt. An einer falschen Formatierung der Spalte B scheint es nicht zu liegen....
Sub Pruefen ()
Dim loLetzte As Long
Dim loLetzte1 As Long
Dim i As Long
Dim j As Long
Dim z As Long
loLetzte = Sheets("Altdaden").Cells(RowsCount, 2).End(xlUp).Row
loLetzte1 = Sheets("Neudaten").Cells(Rows.Count, 2).End(xlUp).Row
z = loLetzte + 1
For i = 2 To loLetzte
For j = 2 To loLetzte1
If Sheets("Altdaden").Cells(i, 2) = Sheets("Neudaten").Cells(j, 2) Then
With Sheets("Neudaten")
.Range(.Cells(j, 1), .Cells(j, 12)).Copy Sheets("Altdaden").Cells(z, 1)
Sheets("Altdaden").Cells(z, 13) = "Neu"
End With
z = z + 1
End If
Next j
Next i
End Sub
Hast du eine Idee?
Danke.

Anzeige
AW: VBA: zwei Sheets vergleichen und Daten kopieren
05.07.2016 13:09:36
baschti007
loLetzte = Sheets("Altdaden").Cells(RowsCount, 2).End(xlUp).Row
der . fehlt bei RowsCount = Rows.Count

AW: VBA: zwei Sheets vergleichen und Daten kopieren
05.07.2016 13:10:11
Werner
Hallo,
da ist mir ein Punkt abhanden gekommen.
Muss Rows.Count heißen.
Gruß Werner

AW: VBA: zwei Sheets vergleichen und Daten kopieren
05.07.2016 14:31:04
jam
Hallo Werner,
danke dir - der Laufzeitfehler ist weg, aber der Vergleich mit der Spalte B ( Inventarnummer) scheint noch nicht richtig zu passen. Es werden trotzdem Inventarnummern bzw. der Datensatz kopiert, die
es bereits im Blatt "Altadten" gab. Somit habe ich quasi in der Spalte B Dubletten.
Es sollen ja keine Datensätze rüberkopiert werden, wenn es die Inventarnummer schon unter "Altdaten" gibt.
Sorry, für die Umstände.
Kannst du dir das nochmal anschauen?

Anzeige
AW: Sorry
05.07.2016 14:45:05
Werner
Hallo,
mein Fehler, ich kopiere ja wenn gleich und nicht wenn ungleich.
Mach hier aus dem = ein 
If Sheets("Altdaden").Cells(i, 2)  Sheets=Sheetaten").Cells(j, 2) Then
Gruß Werner

AW: Sorry
05.07.2016 15:34:26
jam
Werner, sorry...aber jetzt wird der ganze Datenblock aus "Neudaten" zig mal in "Altdaten" kopiert,
so dass ich fast 100.000 Datensätze in dem Tabellenblatt habe....
Es werden also die enstprechenden Datensätze nicht nur einmal kopiert sondern mehrfach.

Anzeige
AW: Sorry
05.07.2016 15:48:47
baschti007
Vielleicht so jam ?
Sub Vergleich()
Dim zells As Range
Dim x As Long
Dim rng As Range
Dim lastn As Long
Dim lasta As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Neudaten")
Set ws2 = Worksheets("Altdaten")
lastn = ws1.Cells(1048576, 2).End(xlUp).Row
lasta = ws2.Cells(1048576, 2).End(xlUp).Row
Set objDic = CreateObject("Scripting.Dictionary")
Dim v, e
With ws2.Range("B2:B" & lasta)
v = .Value
End With
For Each e In v
If Not objDic.Exists(e) Then objDic.Add e, e
Debug.Print e
Next
Set rng = ws1.Range("B2:B" & lastn)
With ws2.Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ws2.Cells.Columns(13).Clear
For Each zells In rng
lasta2 = ws2.Cells(1048576, 2).End(xlUp).Row
If Not objDic.Exists(zells.Value) Then
ws1.Range("A" & zells.Row & ":" & "L" & zells.Row).Copy ws2.Range("A" & lasta2 + 1)
ws2.Range("A" & lasta2 + 1 & ":" & "L" & lasta2 + 1).Interior.Color = 5296274
ws2.Range("M" & lasta2 + 1) = "NEU!!!!"
End If
Next
End Sub

Anzeige
AW: Sorry
05.07.2016 16:16:49
jam
Hallo baschti007,
auch dir danke für deinen Ansatz, aber hier werden wieder die Datenätze von Blatt "Neudaten" in "Aldaten" kopiert ohne das Spalte B verglichen wird.
Vielleicht hilft ein Beispiel:
Ich möchte, dass der Datensatz mit der Inventarnummer 789 in "Altdaten" kopiert wird, der Datensatz
mit der Inventarnummer 123 allerdings nicht.
Tabelle "Altdaten":
Farbe..Inventarnummer..Artikelnr..Kennzeichen
rot.........123............B231......5
blau........456............C234......6
Tabelle "Neudaten":
Farbe...Inventarnummer..Artikelnr..Kennzeichen
rot..........123............B435......0
blau.........789............k654......2
Ich danke euch sehr für eure Bemühungen.

Anzeige
AW: Sorry
05.07.2016 16:17:07
jam
Hallo baschti007,
auch dir danke für deinen Ansatz, aber hier werden wieder die Datenätze von Blatt "Neudaten" in "Aldaten" kopiert ohne das Spalte B verglichen wird.
Vielleicht hilft ein Beispiel:
Ich möchte, dass der Datensatz mit der Inventarnummer 789 in "Altdaten" kopiert wird, der Datensatz
mit der Inventarnummer 123 allerdings nicht.
Tabelle "Altdaten":
Farbe..Inventarnummer..Artikelnr..Kennzeichen
rot.........123............B231......5
blau........456............C234......6
Tabelle "Neudaten":
Farbe...Inventarnummer..Artikelnr..Kennzeichen
rot..........123............B435......0
blau.........789............k654......2
Ich danke euch sehr für eure Bemühungen.

Anzeige
AW: Sorry
05.07.2016 16:40:02
Bastian
Lad mal eine Beispiel mappe hoch damit geht es immer besser =)

AW: Sorry
05.07.2016 22:14:23
jam
Hallo Bastian,
ich glaube ich habe die Ursache gefunden, warum dein Makro bei mir nicht richtig läuft. Die Spalte B aus dem Blatt "Neudaten" hat wohl eine falsche Formatierung.
Ich importiere die neuen Daten mit einem anderen Makro aus einer Access DB.
Die Inventarnummern in Spalte B haben nach dem Import das grüne Merkzeichen (als Text gespeicherte Zahl). Leider reicht es nicht die Spalte bzw. die Nummern entsprechend zu formatieren, sondern ich muss in jeder Zelle einen Doppelklick machen, so dass die Zahl bzw. die einzelne Inventarnr nach rechts springt. Erst wenn ich dass mache, funktioniert auch das Makro wie gewünscht. Das klingt komisch ist aber wirklich so. Ich kann aber nicht bei mehreren hundert Datensätze erst überall einen Doppelklick machen. Habt ihr dafür auch eine Lösung?

Anzeige
AW: Sorry
06.07.2016 00:31:18
jam
Hallo Sebastian, Hallo Werner,
ich wollte gerade einen Haken an das Thema machen,(das Formatproblem ist Dank Werner auch behoben) als mir aufgefallen ist, dass ich etwas gravierendes vergessen habe.
Kann man noch folgende Anforderung in das Script einbauen-also den Gegenabgleich:
Wenn es die Inventarnummer aus dem Blatt "Altdaten" nicht im Blatt "Neudaten" gibt, dann lösche den ganzen Datensatz aus "Altdaten" ( Spalte A -AM).
Entschuldigt bitte, dass ich eure Hilfe so in Anspruch nehme und hoffe das ich eure Geduld nicht überstrapaziere. Ihr habt mir bis jetzt so toll geholfen....
Gruß
jam

Anzeige
AW: Sorry
06.07.2016 08:03:09
baschti007
Ich muss dir sagen, ich bin auch nicht so wirklich der, der Ahnung hat ich Google auch nur und kombiniere die Bausteine musst du auch mal testen funktioniert ganz gut =D
Aber guck mal ob das geht ^^
Ähm wirklich Spalte a-am ? ich habe nun die ganze Zeile gelöscht das ist einfacher ...
https://www.herber.de/bbs/user/106788.xlsm
GRuß Basti

Anzeige
AW: DICKES DANKE !!!!
06.07.2016 10:14:00
jam
Hallo Basti,
das ist doch super! Vielen Dank für deine\eure Hilfe. Echt klasse !
Gruß
jam

Kein Ding
06.07.2016 12:28:00
baschti007
Kein Ding =D Übung macht den Meister oder so was ;)

AW: Sorry
05.07.2016 22:14:04
Werner
Hallo,
liegt daran, dass das absoluter Blödsinn ist, was ich dir da zusammen geschrieben habe. Das kann so gar nicht gehen.
Sorry
Werner

Anzeige
AW: Sorry
05.07.2016 22:21:35
jam
Hallo Werner
kein Problem, das Makro von Bastian ist schon super und würde auch funktionieren-nur muss ich leider jede einzelne Zelle in der Spalte B doppelklicken, da die wohl falsch formatiert sind. Erst dann funktioniert das Makro. Die ganze Spalte als Zahl formatieren bringt nichts-ich muss wirklich doppelklicken. Komisch...
danke dir trozdem für deine Bemühungen

Anzeige
AW: Textzahlen
05.07.2016 22:49:17
Werner
Hallo,
ein neuer Versuch der Hilfe.
nach dieser Codezeile
lasta = ws2.Cells(1048576, 2).End(xlUp).Row
noch das hier rein
With ws1.Range("B2:B" & lastn) 'in B1 Überschrift, Zahlen ab B2, ggf. ändern
.NumberFormat = General
.Value = .Value
Gruß Werner

Anzeige
AW: Textzahlen
06.07.2016 00:03:23
jam
Hallo Werner,
vielen Dank - das hilft sehr.

AW: Gerne u. Danke für die Rückmeldung. o.w.T
06.07.2016 00:06:58
Werner
;
Anzeige
Anzeige

Infobox / Tutorial

VBA: Zwei Sheets vergleichen und Daten kopieren

Schritt-für-Schritt-Anleitung

  1. Öffne Deine Excel-Datei mit den beiden Tabellenblättern "Altdaten" und "Neudaten".

  2. Öffne den VBA-Editor: Drücke ALT + F11.

  3. Füge ein neues Modul hinzu: Rechtsklick auf "VBAProject (DeineDatei)", dann "Einfügen" > "Modul".

  4. Kopiere den folgenden VBA-Code in das Modul:

    Sub DatenVergleichenUndKopieren()
   Dim loLetzte As Long
   Dim loLetzte1 As Long
   Dim i As Long
   Dim j As Long
   Dim z As Long

   loLetzte = Sheets("Altdaten").Cells(Rows.Count, 2).End(xlUp).Row
   loLetzte1 = Sheets("Neudaten").Cells(Rows.Count, 2).End(xlUp).Row
   z = loLetzte + 1

   For i = 2 To loLetzte
       For j = 2 To loLetzte1
           If Sheets("Altdaten").Cells(i, 2) <> Sheets("Neudaten").Cells(j, 2) Then
               With Sheets("Neudaten")
                   .Range(.Cells(j, 1), .Cells(j, 12)).Copy Sheets("Altdaten").Cells(z, 1)
                   Sheets("Altdaten").Cells(z, 13) = "Neu"
               End With
               z = z + 1
           End If
       Next j
   Next i
End Sub

  5. Schließe den VBA-Editor und gehe zurück zu Excel.

  6. Führe das Makro aus: Drücke ALT + F8, wähle DatenVergleichenUndKopieren und klicke auf "Ausführen".

Häufige Fehler und Lösungen

  • Laufzeitfehler 1004: Dieser Fehler tritt häufig auf, wenn die Referenz auf die Zeilenanzahl nicht korrekt gesetzt ist. Stelle sicher, dass Du Rows.Count korrekt schreibst, wie im Beispielcode gezeigt.

  • Doppelte Datensätze: Wenn Datensätze aus "Neudaten" mehrfach in "Altdaten" kopiert werden, überprüfe die Vergleichslogik. Stelle sicher, dass der Vergleich mit <> (ungleich) erfolgt, wie im obigen Code.

  • Falsche Formatierung: Wenn die Daten in der Spalte B als Text gespeichert sind, kann dies zu Problemen führen. Nutze die folgende Zeile im Code, um die Formatierung zu korrigieren:

    With Sheets("Neudaten").Range("B2:B" & loLetzte1)
   .NumberFormat = "General"
   .Value = .Value
End With

Alternative Methoden

  1. Power Query: Nutze Power Query, um Daten zu importieren und zu transformieren, bevor Du sie vergleichst. Dies ermöglicht eine visuelle und benutzerfreundliche Datenaufbereitung.
  2. Excel-Funktionen: Verwende Funktionen wie SVERWEIS oder WENN, um Daten in den Blättern zu vergleichen, anstatt VBA zu verwenden.

Praktische Beispiele

Angenommen, Du hast die folgenden Datensätze in "Altdaten":

Farbe Inventarnummer Artikelnr Kennzeichen
rot 123 B231 5
blau 456 C234 6

Und in "Neudaten":

Farbe Inventarnummer Artikelnr Kennzeichen
rot 123 B435 0
blau 789 K654 2

Nach dem Ausführen des Makros wird der Datensatz mit der Inventarnummer 789 in "Altdaten" kopiert, während der Datensatz mit der Inventarnummer 123 nicht kopiert wird, da er bereits vorhanden ist.

Tipps für Profis

  • Verwende Dictionaries: Nutze ein Dictionary-Objekt in VBA, um die Performance zu verbessern, insbesondere bei großen Datensätzen. Dies reduziert die Anzahl an Schleifen und Vergleichen.
  • Automatisiere die Formatierung: Integriere den Formatierungsschritt direkt im Vergleichsprozess, um sicherzustellen, dass alle Daten korrekt verglichen werden.

FAQ: Häufige Fragen

1. Wie kann ich die Daten in Excel abgleichen, ohne VBA zu verwenden? Du kannst Excel-Funktionen wie VERGLEICH oder SVERWEIS verwenden, um die Inventarnummern zu vergleichen und die entsprechenden Zeilen zu kopieren.

2. Was kann ich tun, wenn die Daten aus einer Access-Datenbank importiert werden? Achte darauf, die Daten nach dem Import zu formatieren. Du kannst den oben genannten VBA-Code verwenden, um die Formatierung in den Zellen zu korrigieren.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige