Hallo,
ich habe folgende Aufgabe:
Erstelle ein Tabellenblatt mit den wöchentlichen Schlusskursen verschiedener Börsenindizes.
Spalte A: Datum des jeweiligen Freitags
Spalte B: Schlusskurs Index 1 vom dazugehörigen Freitag
Spalte C: Schlusskurs Index 2 vom dazugehörigen Freitag
.
.
Spalte …: Schlusskurs Index n vom dazugehörigen Freitag
Jetzt wirds für mich kompliziert:
Jeder Index hat ein eigenes Tabellenblatt. Die Daten werden von excel aus dem Internet bezogen und auf Befehl aktualisiert. Der gewünschte Kurs steht immer in der gleichen Zelle (E12). Das dazu gehörige Datum des Freitags in gleichen Zeile, erste Spalte (A12). (Weil immer Freitags nach Börsenschluss aktualisiert wird)
Ich möchte mir nun ersparen, die einzelnen Kurse immer manuell in das Blatt mit der Übersichtstabelle kopieren zu müssen. Mein Problem ist, dass der jeweilige Wert der Vorwoche nicht verändert werden darf, deswegen komme ich mit einer WENN-Funktion nicht weiter.
Es muss also am Schluss immer der richtige Kurs zum richtigen Datum der jeweiligen Indizes in der Überscihtstabelle erscheinen.
Bestimmt gibt´s auch dafür eine Funktion. Aber welche?
Danke.
Der Money-Schorsch
Hallo Money-Schorsch,
für diese Funktionalität gibt es keine Formel.
Du kannst jedoch den Kopiervorgang per Makro automatisieren. Dabei müssen die Werte aus den einzelnen Tabellen immer in die nächste freie Zeile kopiert werden.
Gruß
Franz
Hallo Franz,
danke für die Info. Habe noch nie mit Makros gearbeitet. Gibt es da irgendwo eine einfache Anleitung im Netz oder muss ich mich mit der Direkthilfe rumschlagen?
Danke
Gruß Money-Schorsch
Hallo Money-Schorsch,
was sind schon einfache Anleitungen?
Für einfache Funktionen kann man ja den Makro-Rekorder benutzen. Er ist auch oft hilfreich, wenn man die Syntax eines VBA-Befehls wissen möchte.
Der Recorder hilft natürlich nicht bei einer struktierten Programmierung. Die VBA-Direkthilfe hilft bei der Anwendung einzelner Methoden und der Abfrage von Eigenschaften, auf Lösungen für die Programmierung von Schleifen und logischen Verzweigungen (If oder Select Case) trifft man aber eher zufällig bzw. man muss wissen, dass es so etwas unter VBA gibt.
Für deine Anwendung hier mal das Grundgerüst einer Prozedur. Die Namen der Blätter und ggf, weitere Case-Zeilen muss entsprechend deiner Arbeitsmappe anpassen/ergänzen.
Gruß
Franz
Sub Indexe\_nach\_Uebersicht()
Dim wksZiel As Worksheet, ZeileZiel As Long, SpalteZiel As Long
Dim wksQuelle As Worksheet
Set wksZiel = ActiveWorkbook.Worksheets("Übersicht")
With wksZiel
'nächste freie Zeile im Zielblatt Spalte A (Spalte mit Datum)
ZeileZiel = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
'Alle Tabellenblätter in der aktiven Mappe abarbeiten
For Each wksQuelle In ActiveWorkbook.Worksheets
'Spalte im Zielblatt festlegen, abhängig vom Namen des Quellblattes
Select Case wksQuelle.Name
Case "Index 01"
SpalteZiel = 2 '- Spalte B
Case "Index 02"
SpalteZiel = 3 '- Spalte C
Case "Index 03"
SpalteZiel = 4 '- Spalte D
Case "Index 04"
SpalteZiel = 5 '- Spalte E
Case Else
'Gilt für alle Blätter aus denen keine Daten in die Übersicht \_
übertragen werden sollen
SpalteZiel = 0
End Select
If SpalteZiel \> 0 Then
'Werte aus Index-Blatt ins Zielblatt übertragen
.Cells(ZeileZiel, 1).Value = wksQuelle.Range("A12") 'Datum
.Cells(ZeileZiel, SpalteZiel).Value = wksQuelle.Range("E12") 'Indexwert
End If
Next
End With
End Sub
Hallo,
ohne Makro mit Handarbeit…
kurz
verknuepfen vereinfachen
nach unten ausfuellen vorbereiten
bleibende Daten festschreiben
in Zukunft wenige klicks
im Einzelnen
In das Tabellenblatt mit den Schlusskursen uebernimmst Du einen Wert aus einem Indexblatt. Zelle anklicken, = und mit der Maus auf das Indexblatt Zelle anklicken. Jetzt ist der erste Zahlenwert uebernommen im Sammelblatt. (ich weiss, naechste Woche wird er sich aendern.)Das machst Du weiter mit allen zu uebernehmenden Zellen, bis die Zeile voll ist. Jetzt kannst Du die Kurse in den Indexblaettern aktualisieren und sie stehen automatisch in der Zusammenfassung drin.
Kopieren vorbereiten - Bezuege fest
Als naechstes gehst Du in jede der Zellen und schreibst jeweils ein Dollarzeichen hinzu, so dass der Bezug fest auf einer bestimmten Zeile und damit Zelle bleibt, wenn Du kopierst. Also da steht =tabelle…E12 und Du machst daraus =tabelle…E$12 Immer vor die Zeilennummer ein Dollar rein. Die ganze Ergebniszeile durch.
Jetzt steht noch dasselbe drin wie vorher.
kopieren
Im naechsten Schritt die ganze Zeile markieren und darunter nochmal hinzukopieren. Jetzt steht alles doppelt untereinander. Kannst auch mehrfach runterkopieren oder nach unten ausfuellen mit der Maus. In jeder Zeile steht dasselbe.
Wenn Du jetzt die Kurse in den Indexblaettern aktualisierst, stehen die neuen Kurse in mehreren Zeilen drin.
Festschreiben gegen weitere Aenderungen
Der Trick ist alt und wenig bekannt. Markiere die oberste Zeile, die mit den Daten, wo keine Aenderungen in Zukunft stattfinden sollen. Dann bearbeiten kopieren und ohne Aenderung der Markierung weiter mit bearbeiten Inhalte kopieren und dann im Fenster anklicken nur Werte keine Formeln dann erst ok.
So werden aus den Formeln einfache Zahlen oder Texte ohne Verknuepfung. Die bleiben. Eventuell muss die Formatierung Nachkomma oder Datum noch angepasst werden.
In Zukunft
hast Du immer in der Ergebnistabelle mehrere Zeilen fuer die neuen Ergebnisse. Wenn nicht, kopiere die letzte mit Formeln mehrfach runter. Dann aktualisieren der Indexblaetter. Festschreiben der obersten Zeile mit den aktuell geholten Daten, speichern, fertig.
Tip
damit die Formeln nicht aus Versehen verlorengehen, leg eine kopierte Zeile weitab nochmal hin, kleine Schrift und blassgrau.
Gruss Helmut
Hallo Franz,
ganz vielen Dank für die Mühe, die Du Dir gemacht hast.
Werde mich an diesem Beispiel jetzt mal in die Materie reinwurschteln.
Gruß vom Money-Schorsch
Hallo Helmut,
danke für den Tipp.
Werde ich auch testen.
Gruß vom Money-Schorsch