Zellinhalt in anderes Arbeitsblatt dynamisch kopieren

Question

Hallo,

leider schaffe ich es nicht den Fehler in meinem Makro zu finden und versuche es daher hier:
Aus der einen Excel-Arbeitsmappe (Mappe 1) soll der Zellinhalt von einer Zelle (z. B. Tabelle 1, G44) in eine andere Tabelle (Mappe2, Tabelle1) kopiert werden.
Das Problem:
die Position an der der Wert in Tabelle 2 eingefügt werden soll, soll sich anhand je eines Kriteriums aus Spalte A und Zeile 1 von Tabelle 2 ergeben. D. h. in Tabelle 2 stehen z. B. Kundennr. in Spalte 1 und Jahr in Zeile 1.
Der Wert von Zelle G44 soll in der Zelle in Tabelle 2 eingefügt werden, für den die dafür nötigen Kriterien aus Tabelle 1 übernommen werden (d. h. Kundennr. und Jahr).
Habe versucht es mit einer verschachtelten for-each-Schleife zu lösen, was leider nicht klappt, da der "next"-Verweis nicht erkannt wird.
Wäre dankbar, wenn mir jemand erklären könnte was ich falsch mache.
Hier der Code:

Sub zelleKopieren()
Dim zelle As Range
Dim spalte As Range
'In Mappe1 ist Tabelle1 aus der Zelle G44 kopiert werden soll
Sheets("Mappe1").Activate

If (("Tabelle1!G44") <> "") Then
'For-each Schleife zur Bestimmung der Zielzelle und zum einfügen
'1. Schleife für das richtige Jahr
For Each zelle In Worksheets("Mappe2").Sheets("Tabelle2").Range("A1:A10000")
'2. Schleife für die richtige Kundennr.
For Each spalte In Worksheets("Mappe2").Sheets("Tabelle2").Range("A1:AF1")

If zelle.Value = Range("Tabelle1!G11").Value Then

If spalte.Value = Range("Tabelle1!G8").Value Then

zelle.Value = Worksheets("Mappe1").Range("G44")

End If
End If

Next zelle 'Fehlermeldung
Next spalte

'sofern nicht kopiert werden kann (weil Zelle G44 leer ist), soll folgender Text erzeugt werden.
Else
Range("Tabelle1!N130") = "konnte nicht kopiert werden"
End Sub

Answer 1

Hallo,

du hast die Next vertauscht. Richtig ist:

Next spalte
Next zelle

Gruß

M.O.

Answer 2

Hallo,

vielen Dank für den Hinweis, jetzt mache ich schon Fortschritte.
Nachdem ich auch noch ein

End if

vor dem Ende des Makros ergänzt habe, kriege ich jetzt leider eine Fehlermeldung beim Start der For-each-Schleife (Index außerhalb des gültigen Bereichs bzw. Laufzeitfehler Nr. 9),
Das Problem ist, dass Mappe 1 und Mappe 2 in zwei verschiedenen Verzeichnissen liegen.

D. h. der Verweis sieht ungefähr so aus:

For Each zelle In Worksheets("C:\Dok\Jahr\Mappe1.xlsx").Sheets("Tabelle1").Range("A1:A1000")

Warum gibt es hier einen Indexfehler ist der Verweis falsch?

Viele Grüße

Answer 3

Hallo,

du musst auch die andere Mappe öffnen. Das kannst du so machen:

Workbooks.Open("C:\Test\Mappe2.xlsx")

Am Ende des Makros kannst du die betreffende Mappe wieder schließen:

Workbooks("Mappe2").Close (False)

Dadurch wird die Mappe ohne zu speichern geschlossen.

Außerdem sprichst du die Arbeitsmappe falsch an. Das geschieht mit Workbooks und nicht mit Worksheets (hatte ich übersehen). Worksheets spricht ebenso wie Sheets die einzelnen Blätter in der Arbeitsmappe an. Außerdem solltest du in diesem Fall bei immer das Workbook mit angeben. Also statt:

If zelle.Value = Range("Tabelle1!G11").Value Then

z.B.

If zelle.Value = Workbooks("Mappe1").Worksheets("Tabelle1").Range("G11").Value Then

oder

If zelle.Value = ThisWorkbook.Worksheets("Tabelle1").Range("G11").Value Then

(Wie du siehst, ziehe ich diese Schreibweise vor).

Gruß

M.O.

Answer 4

Hallo,

Da fehlt bestimmt noch ein .Cells hinten dran.
Also For Each zelle In Worksheets("C:\Dok\Jahr\Mappe1.xlsx").Sheets("Tabelle1").Range("A1:A1000").Cells

Mr. K.

Answer 5

scheint tatsächlich auch ohne zu gehen. Hätt ich jetzt nicht gedacht. Ich bevorzuge das aber mit Anhängsel damit ich sehe, dass ich mich auf eine einzelne Zelle beziehe.

Answer 6

Hallo zusammen,

vielen Dank für Eure Hinweise.
Leider bleibt das Makro nach wie vor beim Start der For-Each-Schleife hängen (Indexfehler 9...).

Beide For-Each-Schleifen habe ich so angepasst:

For Each zelle In Workbooks("C:\Doc\Mappe2.xlsx").Sheets("Tabelle1").Range("A1:AF1")
For Each spalte In Workbooks("C:\Doc\Mappe2.xlsx").Sheets("Tabelle1").Range("A1:A11")

Die Nutzung von

.Cells

hat keine Auswirkung.
Der Verweis auf Mappe 2 ist richtig, da bereits vor Start der Schleifen geöffnet wird.
Wäre nett, wenn Ihr noch weitere Vorschläge hättet.

Viele Grüße

Answer 7

Hallo,

wenn ich dich richtig verstanden habe, sollte es so funktionieren:

Sub zelleKopieren()
Dim zelle As Range
Dim spalte As Range
'In Mappe1 ist Tabelle1 aus der Zelle G44 kopiert werden soll

If Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("G44") <> "" Then
'For-each Schleife zur Bestimmung der Zielzelle und zum einfügen
'1. Schleife für das richtige Jahr
For Each zelle In ThisWorkbook.Worksheets("Tabelle2").Range("A1:A10")

'2. Schleife für die richtige Kundennr.
For Each spalte In ThisWorkbook.Worksheets("Tabelle2").Range("A1:AF1")

If zelle.Value = Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("G11").Value Then

If spalte.Value = Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("G8").Value Then

ThisWorkbook.Worksheets("Tabelle2").Cells(zelle.Row, spalte.Column) = Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("G44")

End If
End If

Next spalte

Next zelle

'sofern nicht kopiert werden kann (weil Zelle G44 leer ist), soll folgender Text erzeugt werden.
Else

Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("N130") = "konnte nicht kopiert werden"

End If

End Sub

Gruß

M.O.

Answer 8

Hallo M. O.,

vielen Dank für die Hilfe.
Jetzt habe ich es mal in einer einzigen Datei unter leicht modifizierter Verwendung Deines Makros versucht, um mein Problem besser erklären zu können.
Es funktioniert leider nur, dass der Wert aus Tabelle 1 übertragen und in Tabelle 2 in der Zelle A1 eingefügt wird.
Eigentlich soll der Wert aus Tabelle 1 an der Position in Tabelle 2 eingefügt werden, die sich aus den beiden Abhängigkeiten Jahr und ID ergibt.
Habe mal ein Beispiel hochgeladen, wie das funktionieren soll:
http://www.file-upload.net/download-10945309/Mappe_test.xlsm.html

">
http://www.file-upload.net/download-10945309/Mappe_test.xlsm.html



In dem Beispiel existieren in Tabelle 1 einige Angaben zu einem Kunden (Jahr und ID) sowie ein Ergebnis.
In Tabelle 2 existiert eine Zeile für die Jahre und eine Spalte für die Kunden-ID.
Der Wert von Tabelle1 Zelle B 3 soll in Tabelle 2 an der Position eingefügt werden, wo Jahr und ID gleich sind (im Beispiel gelb gefärbte Zelle), d. h. Tabelle 2, Zelle E6.

Wenn das Makro mit einer Arbeitsmappe funktioniert, kriege ich es hoffentlich hin es auf zwei Arbeitsmappen in zwei verschiedenen Verzeichnissen zu erweitern.

Gruß

Answer 9

Hallo,

wenn du den Code änderst, indem du zelle und spalte vertauschst, musst du auch in der Zeile, in der die Daten in die Tabelle 2 geschrieben werden entsprechend anpassen.
Hier ist der geänderte Code für deine Beispieldatei:

Sub zelleKopieren()
Dim zelle As Range
Dim spalte As Range

'In Mappe1 ist Tabelle1 aus der Zelle D3 kopiert werden soll

If Workbooks("Mappe_test.xlsm").Worksheets("Tabelle1").Range("B3") <> "" Then
'For-each Schleife zur Bestimmung der Zielzelle und zum einfügen

'1. Schleife für das richtige Jahr
For Each zelle In ThisWorkbook.Worksheets("Tabelle2").Range("B1:X1")

'2. Schleife für die richtige ID
For Each spalte In ThisWorkbook.Worksheets("Tabelle2").Range("A1:A11")

If zelle.Value = Workbooks("Mappe_test.xlsm").Worksheets("Tabelle1").Range("B1").Value Then

If spalte.Value = Workbooks("Mappe_test.xlsm").Worksheets("Tabelle1").Range("B2").Value Then

ThisWorkbook.Worksheets("Tabelle2").Cells(spalte.Row, zelle.Column) = Workbooks("Mappe_test.xlsm").Worksheets("Tabelle1").Range("B3")

End If
End If

Next spalte

Next zelle

'sofern nicht kopiert werden kann (weil Zelle G44 leer ist), soll folgender Text erzeugt werden.
Else

Workbooks("Mappe_test.xlsm").Worksheets("Tabelle1").Range("D3") = "konnte nicht kopiert werden"

End If


End Sub

Angepasst habe ich den folgenden Teil:

.Cells(spalte.Row, zelle.Column)

da du ja mit der Variable spalte in deiner Beispieldatei die Zeilen A1 bis A11 durchsuchst und mit der Variable zellen die Spalten B1 bis X1.

Gruß

M.O.

Answer 10

Hallo M. O.

vielen Dank!!! Das funktioniert einwandfrei.
Allerdings funktioniert es bei der Umsetzung mit zwei Arbeitsmappen leider noch nicht ganz. Das Problem ist die letzte Zeile mit der die Arbeitsmappe 2 geschlossen werden soll:

Workbooks("Mappe2").Close (False)

Es erscheint die Fehlermeldung Nr. 9 Index, obwohl die Datei geöffnet wird und der Wert in die vorgesehene Zelle eingetragen wird. Gut wäre auch, wenn die Arbeitsmappe 2 nach Eintragung die Änderung speichert und wieder geschlossen werden könnte.Müsste hierzu nicht einfach der Code

Workbooks("Mappe2").Close (True)

reichen?

Viele Grüße