Office: (Office 2010) Daten aus anderer Tabelle kopieren und in neue Zeile schreiben

Hallo zusammen,

das Unternehmen in dem ich arbeite bekommt jede KW eine neue Datei mit Lagerdaten etc. bereitgestellt.
Nun sind diese aber in einer Excel Datei total unübersichtlich gespeichert. Also wurde eine andere, übersichtliche Excel Tabelle enworfen, in der die Daten automatisch jede Woche (KW) übernommen werden sollen und in eine neue Zeile (die automatisch erzeugt werden muss) geschrieben werden soll.

Nun habe ich es schonmal hinbekommen, dass die Daten kopiert werden. Nur wenn jetzt in der nächsten Woche eine neue Datei reinkommt, die natürlich einen anderen Namen hat (KW29) muss ich dies im VBA Quellcode wieder angeben. Kann man das auch umgehen?

Außerdem habe ich es noch nicht geschafft, dass die Daten automatisch in eine neue Zeile geschrieben werden, sodass die Summe etc. sich automatisch aktualisiert.

Gerne würde ich die Datei zur Verfügung stellen, nur leider erlaubt das Unternehmen dies nicht.

Eventuell kann mir ja jemand helfen.

Vielen Dank schonmal & Gruß

 

Hallo Jonanony,

wenn sich der Dateiname jedesmal ändert, würde ich den nicht im Code angeben, sondern die Datei mittels "Dateiauswahldialog" auswählen und durch das Makro öffnen lassen.

Und eine neue Zeile einfügen kann man so in der Art:

Code:

.Rows("1:1").Insert Shift:=xlDown

Aber ohne Deinen Code und die beiden Dateien ist Hilfe schwierig.
Frag doch mal Deinen Vorgesetzten, ob Du nicht Kopien von Eurer und der Kundendatei nehmen darfst, in denen Du vorher alle Daten durch Dummys ersetzt hast. Eventuell kannst Du komplette Blätter löschen wenn sie für die Fragestellung nicht von Belang sind.
Und jeweils so ca. 20 Datensätze wären auch völlig ausreichend.
Wichtig ist aber auf jedenfall: Dein kompletter Code muss enthalten sein, und der Tabellenaufbau muss dem Original entsprechen.

 

Hallo fette Elfe,

Vielen Dank für deine Hilfe!
Ich habe mal zwei Testdateien erstellt, die dem Aufbau der eigentlichen Dateien ähneln.
Die Quelldatei soll so in die Zieldatei kopiert werden, sodass sich die Summe der Werte automatisch mit aktualisiert.
Wichtige dabei ist, dass die aktuelle KW automatisch mit dem neuen Datensatz in eine neue Zeile geschrieben wird.
Das Problem mit den verschiedenen Dateinamen ist gelöst. Die Datei wird ihren festen Namen immer behalten.

Vielen Dank & Gruß

 

Hallo Jonanony,

folgende Vorraussetzungen habe ich angenommen:
- die Spalten Deine Testdateien sind identisch mit den Originalen
- unter den KW-Zahlen stehen nirgendwo mehr andere Zahlen in der gleichen Spalte
- da Du in Deinem Zielblatt die Spalte "Jahr" nicht mit übernommen hast, wird es für jedes Kalenderjahr ein neues Blatt in Deiner Zieldatei geben, ansonsten hat das Makro ein Problem mit dem Jahreswechsel (da müsste ich mehr Infos haben um besser helfen zu können)
- in der Zieldatei können komplette Zeilen eingefügt werden, rechts neben den Daten sind keine weiteren Listen die dadurch beeinträchtigt würden

Als Lösung für die automatische Erweiterung der Summenformel schlage ich folgendes vor:
Zwischen Summierung und Daten ist eine Leerzeile, und die Summenformeln schliessen diese Leerzeile mit ein.
Dadurch werden die neuen Zeilen innerhalb (und nicht unterhalb) des zu summierenden Bereiches eingefügt, und die Formel "wächst" mit.

Und hier mein Code:

Code:

Option Explicit

Public Sub DatenKopieren()

Dim loLRowZ As Long         ' letzte Zeile im Zielblatt
Dim loLRowQ As Long         ' letzte zeile im Quellblatt
Dim loCount As Long         ' Schleifenzähler
Dim wkbQ As Workbook        ' Quelldatei
Dim wksZ As Worksheet       ' Zielblatt

Set wksZ = ThisWorkbook.Worksheets("Tabelle1")
Set wkbQ = Workbooks.Open("C:\Users\testuser\Desktop\Quelldatei.xlsx")

' Schleife um im Zielblatt die letzte Zahl in Spalte A zu finden = letzte KW
    With wksZ
        loLRowZ = .Cells(.Rows.Count, 1).End(xlUp).Row
        For loCount = loLRowZ To 2 Step -1
            If (IsNumeric(.Cells(loCount, 1))) And Not (.Cells(loCount, 1) = "") Then
                loLRowZ = loCount
                Exit For
            End If
        Next loCount
    End With
    
    With wkbQ.Worksheets("Tabelle1")
        loLRowQ = .Cells(.Rows.Count, 2).End(xlUp).Row
        ' Schleife durchläuft im Quellblatt alle Zeilen von der letzten bis zur zweiten,
        ' (solange die KW im Zielblatt älter als die KW im Quellblatt ist)
        For loCount = loLRowQ To 2 Step -1
            If IsNumeric(.Cells(loCount, 2)) And Not (.Cells(loCount, 2) = "") Then
                ' Prüfung ob KW im Zielblatt älter als im Quellblatt
                If .Cells(loCount, 2) > wksZ.Cells(loLRowZ, 1) Then
                    wksZ.Rows(loLRowZ + 1).Insert Shift:=xlDown
                    .Range(.Cells(loCount, 2), .Cells(loCount, 5)).Copy
                    With wksZ
                        .Range(.Cells(loLRowZ + 1, 1), .Cells(loLRowZ + 1, 4)).PasteSpecial Paste:=xlPasteValues
                    End With
                Else
                    Exit For
                End If
            End If
        Next loCount
    End With
    
    wkbQ.Close

Set wkbQ = Nothing
Set wksZ = Nothing

End Sub

Deinen Pfad musst Du natürlich noch anpassen.

Wenn Du etwas nicht verstehst, immer fragen.
;O)

 

Hallo fette Elfe,

Vielen Dank für den tollen Code. Er funktioniert super!

Nur leider habe ich jetzt eine andere Quell- sowie Ziel Datei bekommen.
Mir wurde zum Probieren erst einmal eine Beispieldatei gegeben.

In der neuen Quelldatei sind mittlerweile viel mehr Daten, allerdings werden in der Zieldatei nicht alle übernommen.
Wichtig ist das nicht nur die neue KW mit Daten eingepflegt wird, da sich die anderen Daten ebenfalls nochmal ändern können und die Daten in der Zieldatei sich somit alle aktualisieren müssen.

Ich habe jetzt schon den ganzen Morgen versucht deinen Code auf meine Bedürfnisse anzupassen, leider habe ich aber alles nur schlimmer gemacht

Vielleicht findest du ja Zeit dort reinzugucken und hast evtl. eine Lösung.

Vielen Dank & Beste Grüße!

 

Hallo Jonanony,

Sowas kenn ich selber. Auch wenns hier jetzt nicht wirklich hingehört, aber ich kanns mir nicht verkneifen:
Bestell doch mal einen schönen Gruß von mir. Die betreffende Person soll mal folgendes bedenken:
- Wenn Du unter falschen Vorraussetzungen arbeitest, verschwendest Du Arbeitszeit. Das ist ineffektiv.
- Wenn jemand (externes) eine Lösung in seiner Freizeit und ohne Lohn erarbeitet, ist es unverschämt seine Zeit durch falsche Vorgaben zu verschwenden. Würde diese Lösung eingekauft, würde man ja auch direkt die korrekten Vorgaben liefern um die Kosten zu minimieren.
Aber wie gesagt, sowas kenn ich selber auch.

Durch die geänderten Vorgaben sieht der Code jetzt fast komplett anders aus, eine "Anpassung" hätte also nicht viel Sinn gemacht.
;O)

Ich verstehe zwar nicht, wie sich nachträglich die Daten aus vergangenen KW noch ändern können (die müsten doch eigentlich feststehen nachdem sie einmal ermittelt wurden), aber das muss ich auch nicht verstehen, und habs dahingehend angepasst.
Nun werden jedesmal die Daten für alle KW neu einkopiert.
Die Summierungen in der Zieldatei aktualisieren sich problemlos, da die zu summierenden Bereiche durch die vorformatierte Tabelle automatisch angepasst werden. Die Formeln beziehen sich ja nicht mehr auf einen Zellbereich, sondern auf einen Bereich der Tabelle.


Für das Makro müssen die folgenden unbedingt Vorrausetzungen erfüllt sein:
- die Daten beginnen in beiden Dateien in Zeile 4
- die KW stehen in beiden Dateien in Spalte B
- in beiden Dateien muss mindestens eine Datenzeile mit KW-Nr enthalten sein
- die Spaltenbezüge dürfen sich in beiden Dateien nicht ändern

Und vergiss nicht den Pfad zur Quelldatei im Makro anzupassen.
;O)

 

Hallo fette Elfe,

erst einmal vielen Dank für den Code und deine Hilfle! Er funktioniert super.
Nur möchte ich den Code natürlich auch verstehen und bin gerade dabei ihn durchzuarbeiten.
Eine Frage hätte ich da noch: Wenn ich zum Beispiel noch eine neue Spalte (z.B eine vierte Spalte bei den einzelnen Bereichen) hinzufügen möchte, wo müsste ich das angeben?

Vielen lieben Dank & Beste Grüße
jonanony :)

 

Hallo Jonanony,

sorry, in den letzten Tagen hatte ich zuviel um die Ohren, um mich mit Deinem Problem zu beschäftigen.

Das ist gut, sogar sehr gut. ;O)
Ich setze einfach mal voraus, dass Du wenigstens rudimentär Englisch lesen und verstehen kannst? Denn VBA ist "fast" selbsterklärend wenn man Englisch kann. Und einzelne Vokabeln die man nicht versteht, kann man bei LEO gut nachschlagen. ;O)
Ansonsten gibt es im Netz haufenweise "Erklärseiten". Auf Anhieb würde mir zum Beispiel https://de.wikibooks.org/wiki/VBA_in_Excel einfallen. Lass Dich von der Informationsfülle nicht erschlagen. Such einfach immer nach dem gerade benötigten, dann findest Du so langsam den Einstieg.
Ansonsten, wenn Du spezielle Fragen hast, frag ruhig auch hier.
Nur um Dir den kompletten Code jetzt in allen Einzelheiten ausführlich zu erklären, habe ich gerade nicht die Zeit.

Dafür müssen die Bereiche (Range) jeweils angepasst werden.
Oft werden variable Bereiche verwendet, sodass sich das Makro an Veränderungen in den Datenlisten anpassen kann.
Das trifft bei Dir auf die Zeilen bzw. die Zeilenanzahl zu.
Die Spalten habe ich in Deinem Fall aber fest vorgegeben, weil ich angenommen habe die wären unveränderlich. Scheinbar haben Deine Vorgesetzten (oder wer auch immer) die Regeln für das Spiel schon wieder geändert.

Da ich aber keine Ahnung habe, wie sich das bei Euch noch weiter entwickelt, kann ich die Spalten schlecht variabel bzw. selbst anpassend angeben. Ich kenne die zukünftigen Regeln ja nicht.
Deshalb bleiben wir vielleicht einfach bei den festen Spaltenbezügen, und ich erklär Dir den Code an einem Beispiel:

Code:

            ' Bereich "Bestellte Ware" kopieren
            With wksQ
                .Range(.Cells(4, 42), .Cells(loLRowQ, 44)).Copy
            End With
            .Range(.Cells(4, 3), .Cells(loLRowQ, 5)).PasteSpecial Paste:=xlPasteValues

"With wksQ" und "End With" ist die Referenzierung auf das Blatt "wksQ". Dieses wurde zu Anfang des Code definiert, sowohl welches Blatt, als auch in welcher Arbeitsmappe. Diese "Adressierung" ist für Excel also eindeutig und unmissverständlich. Dadurch müssen wir bei allen Zellbezügen oder Bereichsangaben, die zwischen diesen beiden Zeilen stehen, nicht mehr dazuschreiben in welchem Blatt und welcher Mappe sich die Zellen bzw. Bereiche befinden. Allerdings auch nur, wenn wir den kleinen aber wichtigen "Punkt" vor "Cells" (Zelle) oder "Range" (Zellbereich) nicht vergessen. Denn ausgeschrieben würde es "wksQ.Range(wksQ.Cells(4, 42), wksQ.Cells(loLRowQ, 44)).Copy" lauten. Das ist aber zu unübersichtlich, und kann getrost wie oben beschrieben verkürzt werden.

".Range()" gibt einen Zellbereich an. Durch "With wksQ" und den Punkt davor weiß Excel nun also wo dieser Bereich zu suchen ist, aber welche Zellen dieser Bereich beinhaltet, muss innerhalb der Klammern noch angegeben werden.
Dies kann mit unterschiedlichen Schreibweisen geschehen:
".Range("A1:A2")" würde, genau bei bei Formeln in einem Tabellenblatt, die Zellen A1 und A2 beinhalten.
Bei dieser Schreibweise wird immer zuerst die Spalte (A) und dann die Zeile (1) genannt.

Für Makros bevorzuge ich aber die oben im Code gewählte Schreibweise, sobald die Bezüge variabel , also teilweise oder komplett durch Variabeln angegeben werden. Hierbei ist ganz wichtig zu beachten, dass im Unterscheid zur ersten Schreibweise immer zuerst die Zeilen und dann die Spalten angegeben werden.
".cells(2,1)" wäre also die Zelle in der zweiten Zeile der ersten Spalte, also "A2".
".Range(.Cells(4, 42), .Cells(loLRowQ, 44))" dieser Bereich geht also von der Zelle "Zeile 4 , Spalte 42" bis zur Zelle "Zeile loLRowQ , Spalte 44", wobei die Variable "loLRowQ" als die letzte Datenzeile der Liste ermittelt wurde.

Und natürlich kann man auch in der ersten Schreibweise Variablen benutzen, was ich in den meisten Fällen aber für unübersichtlich halte, gerade für Anfänger:
".Range("A1:A" & loLRowQ)" würde den Zellbereich A1 bis Zelle A(Zeile loLRowQ) beschreiben.

Ich hoffe bis hierhin konntest Du mir folgen?
Wenn ja, must Du bei Änderungen der Bereiche also "nur" die entsprechenden Zahlen ändern:

Code:

            ' Bereich "Bestellte Ware" kopieren
            With wksQ
                .Range(.Cells(4, 42), .Cells(loLRowQ, 45)).Copy
            End With
            .Range(.Cells(4, 3), .Cells(loLRowQ, 6)).PasteSpecial Paste:=xlPasteValues

Hiermit wäre der Bereich "Bestellte Ware" im Quellblatt in den vier Spalten 42 bis 45 zu finden, und würde in die vier Spalten 3 bis 6 des Zielblattes kopiert.

Aber Achtung:
Selbst wenn sich im Quellblatt ansonsten nichts an den Spalten ändert, verschieben sich die Spalten im Zielblatt dadurch...
alles was weiter rechts vom Bereich "Bestellte Ware" liegt, muss nun komplett um eine Spalte nach rechts verschoben werden.
Und wenn Du alle Bereiche von 3 auf 4 Spalten erweiterst, summiert sich das.
Der erste Bereich vergrössert sich nur. Beim zweiten Bereich verschiebt sich dadurch erstmal alles nur um eins nach rechts, aber es kommt ja auch noch eine Spalte dazu. Dementsprechend verschiebt sich der dritte Bereich schon um zwei Spalten, usw...
;O)

Soweit alles klar?