Loop durch Tabellenblätter mit Sheets.Index (VBA)

Angeknüpft an den letzten Beitrag zu dynamischen Formeln über mehrere Tabellenblätter, behandelt dieser Beitrag die Thematik, wie mittels einer VBA-Schleife (Loop) durch mehrere Tabellenblätter navigiert werden kann, ohne dass der Code jeweils anzupassen ist. [Eine Einführung zum hier angewandten "For"-Loop - einer VBA-Schleife - findest Du in diesem Beitrag.] Ziel ist es also, weitere Tabellenblätter einfügen zu können, ohne auf diese spezifisch im Code verweisen zu müssen.

Unser Beispielunternehmen weist Zahlen für die jeweiligen Regionen und Unternehmenseinheiten aus. Unten ist ein Beispiel dargestellt, bei dem unterschiedliche Kantone ("Basel", "Bern" und "Zürich") vorhanden sind. Zudem werden die Daten auf Konzernebene aggregiert (Tabellenblatt "Total"):

SheetIndexSheetsA

Wir gehen davon aus, dass jede einzelne Region die gleiche Struktur aufweist und wir demnach denselben Makro-Code auf alle Tabellenblätter anwenden können - ein perfekter Anwendungsfall also für einen Loop! In einem ersten Schritt sollten die notwendigen Befehle für ein einzelnes Tabellenblatt aufgezeichnet, geschrieben und getestet werden. Auf jedem Blatt sollen beispielsweise Spalten ausgeblendet sowie Formatierungen vorgenommen werden. Es bieten sich nun unterschiedliche Schleifen an, die sich über alle gewünschten Tabellenblätter erstrecken. In unserem Fall soll der Loop jedoch dynamisch sein, damit wir jederzeit neue Regionen einfügen können. Verweisen wir im Code demnach explizit auf die aktuell erste Region "Basel", hat dies später allenfalls keine Gültigkeit mehr (Beispiel: Wenn wir "Aargau" einfügen, rückt dieser Kanton an erste Stelle). Im anfangs verlinkten Beitrag zu dynamischen Formeln über mehrere Tabellenblätter haben wir Hilfstabellenblätter eingefügt ("Kanton_AA" und "Kanton_ZZ") und diese am Ende ausgeblendet:

SheetIndexSheetsZ

Durch diesen Mechanismus kann nun ein "For"-Loop angewandt werden. Unser Makro ermittelt den "Sheet.Index" dieser Hilfsblätter; das heisst, an welcher Stelle in der Datei diese Tabellenblätter stehen. Wir definieren hier einerseits die Variablen des Typs "Integer" (vereinfacht: Zahlen) im Makro:

Dim SheetIndexStart As Integer
Dim SheetIndexEnd As Integer

Danach kann der Indexwert dieser beiden Tabellenblätter ermittelt werden:

SheetIndexStart = Sheets("Kanton_AA").Index
SheetIndexEnd = Sheets("Kanton_ZZ").Index

In unserem Beispiel wird für "Kanton_AA" der Wert "2" als Variable abgespeichert, für "Kanton_ZZ" ist dies der Wert "6". Nun kann der "For"-Loop eingesetzt werden, der den Code mit den Befehlen für die Tabellenblätter umfasst:

For x = SheetIndexStart + 1 To SheetIndexEnd - 1
Sheets(x).Activate
'Befehle zur Bearbeitung der Tabellenblätter
Next

Da wir die Hilfsblätter nicht zwingend bearbeiten müssen, kannst Du entweder direkt bei der Definition der Variablen "+1" addieren respektive "-1" subtrahieren, oder wie im Code oben beim Beginn des "For"-Loop die Index-Zahl um eins erhöhen und reduzieren. Mit "Sheets(x).Activate" wird dann in jedem Durchlauf der Schleife jeweils das entsprechende Tabellenblatt aktiviert und bearbeitet. Beachte, dass Du die Hilfstabellenblätter "Kanton_AA" und "Kanton_ZZ" allenfalls zuerst durch das Makro einblenden musst. Füge hier zu Beginn Deines Makros einfach ein:

Sheets("Kanton_AA").Visible = True
Sheets("Kanton_ZZ").Visible = True

... und am Ende dasselbe, mit dem Boolean-Wert "False", damit die Blätter direkt wieder ausgeblendet sind.

Fügst Du nun eine neue Region (z.B. "Genf" oder "Aargau") ein, wird das Makro dank der Ermittlung des "Index-Wertes" auch diese Tabellenblätter in der Loop-Bearbeitung berücksichtigen können. Entsprechend reagiert die Selektion, falls Du weitere Tabellenblätter weiter vorne in der Datei einfügst (beispielsweise eine weitere Auswertung neben dem "Total") oder die Kantone mitsamt den beiden Hilfsblätter an eine andere Stelle innerhalb der Datei verschiebst.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.