Excel: automatischer Datenübertrag aus vorherigem Blatt

Angenommen, Sie haben 12 oder 52 Tabellenblätter, jeweils eins für jeden Monat oder gar für jede Woche. In Spalte D sind die monatlichen oder wöchentlichen Werte addiert. Die nächsten Blätter wollen Sie nun so vorbereiten, dass in jedem nächsten Blatt in Spalte B die Zahlen aus Spalte D des vorherigen Blattes erscheinen. 

Klingt einfach, ist es aber nicht. Denn ein normaler Verweis im Stile von «='Tabelle1'!D2» würde sich beim Kopieren eines Blattes oder der Zellen nicht anpassen. Er hiesse auch in Tabelle2 bis Tabelle12 immer noch «Tabelle1». Sie müssten den Verweis in allen Formeln manuell korrigieren – und zwar in jedem einzelnen Blatt!

Darum haben wir hier zwei Ansätze; einen mit dem Makro eines unserer Forumsnutzer und einen anderen mit einer Formel (die aber auch ihre Tücken hat).

Das Makro

Öffnen Sie mit Alt+F11 den VisualBasic-Editor. Doppelklicken Sie in der linken Spalte auf Diese Arbeitsmappe und fügen Sie den untenstehenden Quellcode ins Fenster ein. Wichtig hierbei: Passen Sie im letzten Absatz «Range("D2:D5")» auf den Zellbereich an, der kopiert werden soll. Und bei «Range("B2")» geben Sie die Adresse der obersten Zelle an, in welche die kopierten Daten eingefügt werden sollen.

Gehen Sie zu Datei/Schliessen und zurück zu Excel. Speichern Sie die Datei als Dateityp .xlsm, damit Excel das Makro später auch wirklich ausführt.

Sub VorDatenKopieren()

asi = ActiveSheet.Index

nasi = Sheets(asi).Name

If asi = 1 Then

MsgBox "Zu " & nasi & " gibt es keine vorherige Tabelle!", _

48, Environ("UserName")

Exit Sub

End If

asiv = ActiveSheet.Index - 1

nasiv = Sheets(asiv).Name

mgb = MsgBox("Daten werden kopiert von " & nasiv & " nach " & nasi & Chr(13) & _

"Ist das OK?", 36, Environ("UserName"))

If mgb = 7 Then Exit Sub ' 7 = nein

Sheets(asiv).Select

Range("D2:D5").Copy

Sheets(asi).Select

Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub

Fortan können Sie in einem neuen Tabellenblatt via Entwicklertools das Makro namens VorDatenKopieren starten. Es fragt zurück, ob die Daten vom vorherigen Blatt ins aktuelle Blatt geschrieben werden sollen und überträgt diese vom definierten Zellbereich in den ebenfalls definierten Zielbereich. Dabei schreibt es die Daten übrigens als reine Werte hinein, nicht als Formeln.

Alternative ohne Makro

Man findet im Netz verschiedene Lösungen mit Makro. Wenn es aber auf gar keinen Fall ein Makro sein darf, ginge es zur Not auch mit einer relativ einfachen Formel. Die hat aber ein paar kleinere Haken:

• Die Namen der Tabellenblätter müssen zwingend mit den Blattnummern übereinstimmen. Das heisst: Das erste Blatt muss exakt den Tabellennamen 01 tragen, das zweite 02 und so weiter.
• Die Blätter müssen auch in der korrekten Reihenfolge (den Nummern nach aufsteigend) eingeordnet sein. Sie dürfen diese also nicht verschieben, sonst entstehen Zirkelbezüge.

Wenn die Voraussetzungen gegeben sind, können Sie im zweiten Blatt in der ersten Zelle, in der die Daten vom «Vorblatt» übernommen werden müssen, die folgende Formel hineinschreiben; passen Sie nur die beiden Zellbezüge an. Der erste ist die Spaltenangabe (hier Spalte D) in "!D"), der zweite (hier z.B. D2) ist die erste Zelle, aus der Excel die Daten kopieren soll: 

=INDIREKT(TEXT(BLATT()-1;"00")&"!D" & ZEILE(D2))

Kopieren Sie die Zellen herunter. Wenn Sie diese Formeln nun in weitere Blätter (mit korrekt hochnummerierten Namen) kopieren, werden sich die Zellen D2 bis D5 immer den Inhalt aus B2 bis B5 des vorangehenden Tabellenblatts holen. (PCtipp-Forum)