Zeilen per VBA kopieren

[Tyler]

Grüezi,
Leider scheitere ich wieder an meinen mangelhaften Makro Kenntnissen  und hoffe das vielleicht einer von euch mir die Augen öffnen könnte.
Ich möchte per VBA ganze Zeilen aus mehrehren Dateien in eine Datei Kopieren.
Beispiel:
Aus - Mappe1 / D5 – W5 -  > in – Mappe Test / E8 – X8
Aus - Mappe2 / B3 – U3 -   > in – Mappe Test / E9 – X9
Aus - Mappe2 / A8 – T8 -   > in – Mappe Test / E10 – X10
 
Ich habe auch Formeln gefunden und anpassen können, nur nicht soweit, dass der Code alles so wie erwartet umsetzt.
 

Code:

Public Sub Worksheet_Activate()
    Dim strPath As String, strFile As String
    strPath = " C:\Ordner\Dateien "
    strFile = "Mappe1.xlsx"
    Worksheets(1).Range("A10").Value = GetValue(strPath, strFile, "Tabelle1", "A1")
    Worksheets(1).Range("E2").Value = GetValue(strPath, strFile, "Tabelle3", "D5")
   
    strPath = "C:\Ordner\Dateien"
    strFile = "Mappe2.xlsx"
    Worksheets(1).Range("C18").Value = GetValue(strPath, strFile, "Tabelle3", "D5")
End Sub
Private Function GetValue(ByVal path As String, ByVal file As String, ByVal sheet As String, ByVal ref As String) As String
    Dim arg As String
    arg = "'" & path & "\[" & file & "]" & sheet & "'!" & Range(ref).Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)
End Function

  
Diese Formel funktioniert soweit, dass in eine andere Zelle Kopiert wird als ausgelesen.
Allerdings nicht Zeilen weise. Und um jede einzelne Zelle im Code zu beschreiben wären
es einfach zu viele Daten.

Code:

Public Sub CommandButton1_Click()
  Dim Dateiname As String
  Dim sPfad As String
  Dim rngZelle As Range
  Dateiname = "Mappe2.xlsx"
  sPfad = " C:\Ordner\Dateien \"
  For Each rngZelle In Range("D5:L5")
    rngZelle = GetValue(sPfad, Dateiname, "Tabelle3", rngZelle.Address)
  Next rngZelle
End Sub
 
Public Function GetValue(path$, file$, sheet$, range_ref$)
  Dim arg As String
  If Right(path, 1) <> "\" Then path = path & "\"
  If Dir(path & file) <> "" Then
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(range_ref).Range("A1").Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)
  End If
End Function

 
Und mit dieser Formel werden die Zellen Zeilen weise (D5:L5) kopiert.
Allerdings gelinkt es mir nicht, die Werte in eine Anderen Zeilen Reihe zu kopieren (D5:L5 > C8:K8)
und auch nicht das mehre Dateien angesprochen werden.
 
Hat jemand vielleicht einen Tipp?
Ich habe irgendwie ein Brett vorm Kopf :20:

Danke
Tyler

[Gast 123]

Hallo Tyler

versuchen wir mal zusammen die Aufgaben zu lösen.  Zuerst das 2.Makro, ist glaube ich am einfachsten!
Aendere die Zielzelle über   rngZelle.Offset(3, -1) = GetValue    Die 3 heisst, 3 Zeilen nach unten, -1 um eine Spalte zurück. 
Für Spalte "F" waere es:  Offset(3, 1)   Ich hoffe du hast das Offset System verstanden!, und kannst es jetzt selbst beliebig aendern!

Die Formel Zeilenweise einsetzen geht auch bei Relativer Formel.  Dazu müsstest du in den Formeln das Absolut Zeichen löschen, achte auf das Dollar "$" in den Formeln!  Wenn du das durch Suchen und Ersetzen löschst kannst du die Zeile nach unten kopieren.  Ohne $ sollte es klappen, probiere es bitte aus.  Würde mich freuen wenn es danach klappt.

mfg  Gast 123

[Tyler]

Hallo Gast :) 

Das mit der Offset änderung werde ich testen, dass mit dem System heißt dann:
3 = 3 Zeilen runter -      wenn es nun 2 Zeilen hoch gehen soll? 
-1 = 1 Spalte zurück springen -    0 = selbe Spalte   +1 = 1 Spalte vorwärts usw.? 

Die relative Formel meinst du nicht auf den Code bezogen oder? 
Irgendwie irritiert mich dein punkt "Zeile nach unten Kopieren" 

Und in der 2 Makro fehlt mir noch der Bezug zu weiteren Dateien (Mappe1 / Mappe2 etc.) 

Inzwischen habe ich natürlich weiter rumprobiert und festgestellt, dass ich noch ein weiteres Problem habe:
Wie schon erwähnt möchte ich z.B. die Werte aus Mappe2 Zeile 8  E8 - X8 auslesen... 
Allerdings kann es vorkommen das in Mappe2 eine weitere Zeile eingefügt wird und die benötigten Werte nun in Zeile 9 anstatt 8 steht (E9-X9). Kann das irgendwie in der Makro abgebildet werden? 

Danke für die Unterstützung 
Schönen Abend noch 
Tyler

[Gast 123]

Hallo Tylor

wenn es nun 2 Zeilen hoch gehen soll?

mich freut das du dich mit dem VBA System beschaeftigst.  Zu dieser Frage:  Offset(0, -2)  geht nach oben!! = Rückwaerts
Die genaue Definition ist:  Offset(Z, S)   dabei ist z=Zeile und S=Spalte.  Man kann aber nicht über den Blattrand hinaus gehen!!
Range("A1").Offset(1, 1) geht schraeg nach unten, in die naechste Spalte!  Range("A1").Offset(-1, -1)  führt zum Laufzeitfehler, weil der Blattrand mit "A1" bereits erreicht ist!!

Mit dem $ Zeichen meine ich die Formel, die Daten aus der geschlossenen Mappe holen soll.  Excel setzt immer das $ Zeichen ein!  Damit ist diese Zelle als Absolute Adresse festgelegt.  Löscht man das Zeichen und kopiert die Formel nach unten veraendert Excel auch die Zellen in der Quelldatei.  Probier es bitte einfach mal aus.

Zur Mappe 2, wenn Zeilen verschoben sind.  Ich würde ein Hilfblatt anlegen und mir einen grösseren Bereich hineinkopieren. Dann sieht man an den Daten in welcher Zeile die Werte stehen, und kann die Formel in der Zieltabelle entsprechend korrigieren. Die Hilfstabelle muss absolut leer sein und die 1. Formel bei "A1" beginnen und sich auf "A1" in der Quelle .beziehen!  Dann sieht man die Zellen in beiden Tabellen 1:1. So würde ich es machen.

mfg  Gast 123

[Gast 123]

Nachtrag

beim Experimentieren mit VBA Makro oder Formeln sollte man zur Sicherheit immer eine Kopie der Originaldatei erstellen, und jede Aenderung in der Kopie testen.  Sollte es nicht klappen, oder im makro ein Fehler sein, zerstört man sich so wenişgstens keine Originaldaten.  Besonders am Arbeitsplatz bitte an Sicherheit denken!

Ich habe mir schon oft genug beim Entwickeln selbst Dateien erschossen!!!  Bei einer Kopie ist das egal!!

mfg  Gast 123

[Steffl]

@Gast 123,

Zu dieser Frage:  Offset(0, -2)  geht nach oben!! = Rückwaerts
Die genaue Definition ist:  Offset(Z, S)   dabei ist z=Zeile und S=Spalte.  Man kann aber nicht über den Blattrand hinaus gehen!!

da möchte ich dir widersprechen, bei Offset(0, -2) geht es nach links und nicht nach oben.

[Tyler]

Guten Morgen zusammen,
 
Und Vielen Dank. :43:
Das mit dem Offset System war ein Hilfreicher Tipp.
Und funktioniert Super, auch das mit dem Auslesen auf Mehreren Dateien ist mir nun gelungen.
Vielen Dank für die Unterstützung.
 
Nun fehlt mir nur noch der Punkt, wenn sich die Zeile verschoben hat. Dein Vorschlag mit dem Hilfsblatt verstehe ich leider nicht ganz?
Meine Vorstellung war, dass irgendwie mit in dem Makro abzubilden.
Sprich: Werte in D5-K5 > eine Zeile wird eingefügt < nun müssen die Werte aus D6-K6 entnommen werden.
 
Lässt sich das abbilden? Oder sollte ich mir was Anderes überlegen?
Eine direkte Idee habe ich allerdings nicht.
 
Danke Euch :43:
Tyler

[Gast 123]

Hallo

@Stefan  Sorry, du hast Recht!!  War schon spaet am Abend, Flüchtigkeitsfehler!  Danke für deine Aufmerksamkeit.

@Tylor   gibt es eine Möglichkeit nach dem kopieren an Hand der Werte festzustellen das sich die Zeile verschoben hat??
Dann könnte man mit IF Then auswerten ob man die naechste Zeile kopieren muss.  Ich kenne die Datei ja nicht.

mfg  Gast 123

[Tyler]

Hallo, 

Die Reihe aus der die Werte entnommen werden sollen (z.B. C5-X5) steht in der Zelle davor (B5) immer SUM. 
Könnte das hilfreich sein? 

LG
Tyler

[schauan]

Hallöchen,

ob eine Zelle in eine andere Zeile verschoben wurde, lässt sich einfach per Formel feststellen.

Schreibe in B1 die Formel =A1 und in C1 die Zahl 1.

Schiebe nun vor Zeile 1 eine Zeile oder auch nur eine Zelle ein.

Nun siehst Du den Unterschied

Hat lediglich den Nachteil, wenn Du übermorgen oder gleich die eingefügte Zeile oder Zelle wieder löschst oder wie auch immer die Zelle zurück in die Zeile 1 bringst, dass Du dann keinen Unterschied mehr sieht. Aber vielleicht ist das dann auch nicht nötig.