Andreas Unkelbach
Werbung


Dienstag, 4. Juni 2013
18:20 Uhr

Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter

Ausgangslage
Bei umfangreichen Tabellen (bspw. mehrere Tabellenblätter innerhalb einer Excelmappe) ist es oft sinnvoll im ersten Blatt eine Übersicht mit Definitionen oder ein Inhaltsverzeichnis anzulegen. Sinnvollerweise kann hier über die Funktion HYPERLINK auch auf die dahinter liegenden Excelmappen verwiesen werden. Dieses kann aber bei mehreren Tabellenblättern recht mühsam sein entsprechend diese anzulegen.



Im Beispiel gibt es ein Tabellenblatt für die Gesamtsicht und eines für einzelne Abteilungen, Fachbereiche und Verwaltung.

Lösung:
Durch einen Beitrag im VBA Entwickler-Treff von XING bin ich auf den Beitrag "Inhaltsverzeichnis für Arbeitsmappe automatisch erstellen" gestossen und habe dieses Makro für mich wie folgt angepasst.




    Sub Inhaltsverzeichnis()
    Dim intTab As Integer
    Dim tbl As Worksheet
    Dim intZeile As Integer

    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2

    ' eingefügtes Blatt "Inhaltsverzeichnis" nennen
    Worksheets(1).Name = "Inhaltsverzeichnis"
    ActiveSheet.Name = Worksheets(1).Name
    Cells(1, 1).Value = "Enthaltene Blätter"

    For intTab = 2 To ActiveWorkbook.Worksheets.Count
    tbl.Cells(intZeile, 1).Value = Worksheets(intTab).Name

    tbl.Cells(intZeile, 1).Hyperlinks.Add _
    Anchor:=tbl.Cells(intZeile, 1), Address:="", SubAddress:= _
    "'" & Worksheets(intTab).Name & "'!A1", _
    ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
    TextToDisplay:=Worksheets(intTab).Name


    intZeile = intZeile + 1
    Next intTab

    ' Spaltenbreite fixieren
    Worksheets(1).Cells.EntireColumn.AutoFit

    End Sub


In dieser Variante wird ein Blatt mit der Bezeichnung Inhaltsverzeichnis angelegt und mit entsprechenden Hyperlinks ergänzt. Dieses Beispiel ist im weiteren auch dokumentiert.

Angepasste Version:
Hier wird als Überschrift die Zelle A1 jeden Blattes übernommen und das erste Tabellenblatt ohne Namenszuweisung angelegt. Hierdurch kommt es zu keinen Fehler, solle ein Blatt mit der Bezeichnung Inhaltsverzeichnis schon vorhanden sein.


    Sub Inhalt_mit_Überschrift()
    Dim intTab As Integer
    Dim tbl As Worksheet
    Dim intZeile As Integer

    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2


    ' Zellenüberschriften

    ActiveSheet.Name = Worksheets(1).Name
    Cells(1, 1).Value = "Überschrift"
    Cells(1, 2).Value = "Link"
    Cells(1, 1).Font.Bold = True
    Cells(1, 2).Font.Bold = True


    For intTab = 2 To ActiveWorkbook.Worksheets.Count

    ' Ausgabe der Zelle A1 eines jeden Arbeitsblattes als Überschrift


    tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a1"

    'Übernahme der Registerblattfarbe als Schriftfarbe


    tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color


    'Setzen eines Hyperlinks auf Tabellenblatt

    tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name

    tbl.Cells(intZeile, 2).Hyperlinks.Add _
    Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
    "'" & Worksheets(intTab).Name & "'!A1", _
    ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
    TextToDisplay:=Worksheets(intTab).Name


    intZeile = intZeile + 1
    Next intTab


    Worksheets(1).Cells.EntireColumn.AutoFit

    End Sub


Durch dieses Makro wird die vorhandene Tabelle um ein weiteres Blatt erweitert und in diesen werden alle vorhandenen Blätter mit einen entsprechenden Link aufgelistet.

Zur Erstellung von Makros ist bei Excel 2007 erst einmal die Registerkarte "Entwickleroptionen" unter den Punkt "Die am häufigsten verwendeten Optionen bei der Arbeit mit Excel" unter der Kategorie "Häufig verwende" zu aktivieren.

Nun findet sich der Makroeditor in der Symbolleiste "Entwicklertools" und kann hier direkt über Makros aufgerufen werden. In Excel 2010 ist dieser in der Symbolleiste Ansicht zu finden.

Hier kann nun im Feld Makroname ein Name bspw. Inhaltsverzeichnis eingetragen werden und durch den Klick auf "Erstellen" ein Makro angelegt werden.

Dabei wird durch sub Name und endsub() das entsprechende Makro angelegt. Hier kann das oben angegebene Makro eingefügt werden.

Makrocode einfügen

Nun kann das VisualBasic Fenster geschlossen werden und zur eigentlichen Tabelle zurückgekehrt werden.

Das Makro ist nun direkt gespeichert und kann über den Makroeditor wiederum durch "Ausführen" gestartet werden.

Makro ausführen

Hierbei hat das Makro unabhängig vom gewählten Makronamen die im Code mit sub Name() angegebene Bezeichnung. Im Beispiel also Inhaltsverzeichnis.

Im Ergebnis sieht das erste Tabellenblatt nun wie folgt aus:
Inhaltsverzeichnis

Durch einen Klick auf einen entsprechenden Link kann auch direkt auf das Exceltabellenblatt gewechselt werden.

Der Vorteil dieses Makro ist, dass es unabhängig von bereits vorhandenen Tabellenblättern angelegt werden kann und in der Reihenfolge der Tabellenblätter eine Übersicht über die einzelnen Tabellenblätter erstellt.

Es ist sinnvoll dieses Makro in Verbindung mit den im Artikel Office Integration - Excelansicht in SAP und Daten kopieren nach Excel vorgestellten Makros zur Übernahme von Daten aus Buchhaltungssystemen in eine persönliche Makroarbeitsmappe (siehe Artikel Excel Umgang mit Makros und Visual Basic for Applications (VBA) zu übernehmen.

Zusammenfassung (Zwei Makros mit A1 oder A2 als Überschrift)

    Sub Inhalt_mit_Überschrift_aus_a1()
    '
    ' Inhalt_mit_Überschrift_aus_a1 Makro
    ' Erstellt ein Inhaltsverzeichnis aller Tabellenblaetter und verwendet als Überschrift die Zelle A1 eines jeden Tabellenblattes
    '

    '
    Dim intTab As Integer
    Dim tbl As Worksheet
    Dim intZeile As Integer
    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2
    ActiveSheet.Name = Worksheets(1).Name
    Cells(1, 1).Value = "Überschrift"
    Cells(1, 2).Value = "Link"
    Cells(1, 1).Font.Bold = True
    Cells(1, 2).Font.Bold = True
    For intTab = 2 To ActiveWorkbook.Worksheets.Count
    tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a1"
    tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color
    tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name
    tbl.Cells(intZeile, 2).Hyperlinks.Add _
    Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
    "'" & Worksheets(intTab).Name & "'!A1", _
    ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
    TextToDisplay:=Worksheets(intTab).Name
    intZeile = intZeile + 1
    Next intTab
    Worksheets(1).Cells.EntireColumn.AutoFit
    End Sub



    Sub Inhalt_mit_Überschrift_aus_a2()
    '
    ' Inhalt_mit_Überschrift_aus_a2 Makro
    ' Erstellt ein Inhaltsverzeichnis aller Tabellenblaetter und verwendet als Überschrift die Zelle A2 eines jeden Tabellenblattes
    '

    '
    Dim intTab As Integer
    Dim tbl As Worksheet
    Dim intZeile As Integer
    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2
    ActiveSheet.Name = Worksheets(1).Name
    Cells(1, 1).Value = "Überschrift"
    Cells(1, 2).Value = "Link"
    Cells(1, 1).Font.Bold = True
    Cells(1, 2).Font.Bold = True
    For intTab = 2 To ActiveWorkbook.Worksheets.Count
    tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a2"
    tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color
    tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name
    tbl.Cells(intZeile, 2).Hyperlinks.Add _
    Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
    "'" & Worksheets(intTab).Name & "'!A2", _
    ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
    TextToDisplay:=Worksheets(intTab).Name
    intZeile = intZeile + 1
    Next intTab
    Worksheets(1).Cells.EntireColumn.AutoFit
    End Sub



Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Anonym am 9.1.2014 um 10:37 Uhr
Habe ich dringend gesucht!


Anonym am 27.1.2014 um 16:27 Uhr
Habe ich dringend gesucht!


PJ am 27.1.2014 um 16:31 Uhr
Hallo,

vielen Dank für das Makro!
Gibt es evtl. ein Makro "Zurück zum Inhaltsverzeichnis" ?
D.h. ich springe per Hyperlink auf eine beliebige Seite um z.B. dort etwas einzutragen. Danach möchte ich wieder zurück ins Inhaltsverzeichnis.

Danke und Grüße

PJ


Andreas Unkelbach Homepage am 27.1.2014 um 21:58 Uhr
Hallo PJ,

Grundsätzlich besteht auch hier die Möglichkeit.

Der passende Ansatz wäre wohl (ohne Anspruch auf Coderichtigkeit):
    For intTab = 2 To ActiveWorkbook.Worksheets.Count
    Sheets(Worksheets(intTab)).Select

    Und hier eine Aufzeichnung des Setzen eines Hyperlink zum Tabellenblatt Inhaltsverzeichnis

    Next intTab
Die Aufzeichnung von Makros ist recht gut im Artikel Excel Umgang mit Makros und Visual Basic for Applications (VBA) beschrieben, jedoch würde ich dieses Vorgehen als kritisch betrachten, da hierdurch ggf. Inhalte in den einzelnen Tabellenblättern überschrieben werden könnten.


Florian am 1.2.2014 um 19:27 Uhr
Hi,
funktioniert PERFEKT, vielen Dank!!

Beste Grüße
Florian


Anonym am 25.2.2014 um 12:38 Uhr
Habe ich dringend gesucht!


T am 24.3.2014 um 23:08 Uhr
Hallo,
ich hab deinen Code eins zu eins übernommen, funktioniert einwandfrei,
außer
1. das mit dem "Zurück zum Inhaltsverzeichnis" ... Fehlermeldung: Sheets(Worksheets(intTab)).Select
kann mir jemand ausführlich erklären wo ich den Code einfügen kann?

2. ich hab den code folgendes erweitert
Worksheets(1).Name = "Inhaltsverzeichnis"
ActiveSheet.Name = Worksheets(1).Name
Cells(1, 1).Value = "Inhaltsverzeichnis"
Cells(2, 1).Value = "Title"
Cells(2, 2).Value = "Link"
Cells(1, 1).Font.Bold = True
Cells(2, 1).Font.Bold = True
Cells(2, 1).Font.Size = 16
Cells(2, 2).Font.Bold = True
Cells(2, 2).Font.Size = 16
Compiliert fehlerfrei, hier erscheint nur der Inhaltsverzeichnis; Title und Links verschwinden
vielen dank


Andreas Unkelbach Homepage am 26.3.2014 um 11:25 Uhr
Hallo T,

zu den 2. von dir genannten Punkt.

Die Anweisung cells(Z,S) weist innerhalb des Blattes die Zelle aus. Hierbei steht Z für die soundvielte Zeile und S für die entsprechende Spalte.

Sofern du Title und Link nach Inhaltsverzeichnis setzen magst (also in den Zellen A2 und A3 müsste die Anweisung
Cells(1, 2).Value = "Title"
Cells(1, 3).Value = "Link"
lauten.

Zum 1. Punkt könnte das Makro, nachdem das Blatt Inhaltsverzeichnis angelegt wurde wie folgt funktionieren:

Sheets("Inhaltsverzeichnis").Select
For intTab = 2 To ActiveWorkbook.Worksheets.Count
Sheets(Worksheets(intTab)).Select
ActiveCell.FormulaR1C1 = _
"=HYPERLINK(Inhaltsverzeichnis!RC,""Inhaltsverzeichnis"")"
Next intTab

Damit sollte in der Zelle A1 eines jeden Blattes ein Link auf das Inhaltsverzeichnis gesetzt werden.

Ggf. sollte das Makro noch an den eigenen Bedürfnissen angepasst werden.

Viele Grüße
Andreas Unkelbach



Anonym am 12.4.2014 um 12:14 Uhr
Danke!


T am 8.5.2014 um 13:31 Uhr
Hallo Andreas,
vielen dank, funktioniert einwandfrei!
Meine nächste Frage ist:
Wenn ich eine neue Tabelle in der Mappe einfüge, muss ich immer den Inhaltsverzeichnis löschen und noch mal erzeugen?
Gibt es eine andere möglichkeit, dass die Inhaltverzeichnis, sich automatisch aktualisiert???
Vielen Dank


Andreas Unkelbach Homepage am 8.5.2014 um 13:47 Uhr
Hallo T,

das von mir eingesetzte Makro ist so angelegt, dass es durch die Zeilen:
    Dim tbl As Worksheet
    Dim intZeile As Integer
    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2
    ActiveSheet.Name = Worksheets(1).Name
auf jeden Fall ein neues Tabellenblatt anlegt, welches vor allen anderen Tabellenblätter angelegt wird.

Sofern aber das vorhandene Inhaltsverzeichnis aktualisiert werden soll könnte die Anweisung
    Set tbl = Worksheets.Add(before:=Worksheets(1))
entfernt werden und direkt über
    ActiveSheet.Name = Worksheets(1).Name
das erste Tabellenblatt in der Liste für die Erstellung des Inhaltsverzeichnises genutzt werden.

Da ich dieses Makro jedoch in meiner persönlichen Makroarbeitsmappe hinterlegt habe (siehe Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" ist es für mich einfacher, das vorhandene Inhaltsverzeichnis zu löschen und dieses neu zu erstellen.

Da ich die Farben der Registerblätter auch gleichzeitig für den Bearbeitungsstatus verwende (ROT = noch keine Daten, GELB = Kontrolle erforderlich und GRÜN = alles in Ordnung) ist dieses auch sinnvoll um über die Anweisung
    tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color

gleichzeitig einen Überblick über den derzeitigen Bearbeitungsstand zu erhalten.

Viele Grüße
Andreas Unkelbach


Daniela am 16.7.2014 um 09:23 Uhr
Suuper! Vielen Dank!
You made my day!
Nachdem ich ca. 20 Tabellenblätter hatte und es langsam unübersichtlich wurde. Benutze Excel als ToDo Liste und alle Subtasks zu einem Hauptprojekt stehen in den einzelnen "Kundenblättern".
Durch die Variante mit der Zelle A1 konnte ich nun Projektname, Kundenname, Datum und nächste Fälligkeit super einsammeln lassen!
Sollten Sie mal in der Nähe von 48683 sein gibt's nen Kaffee :)
Einfach anmailen ;)

LG
Daniela


Daniela am 16.7.2014 um 09:23 Uhr
PS.: Wenn man dann schnell zu Blatt 1 möchte: Rechtsklick auf die Blätterwechsel Pfeile!


Herby am 21.7.2014 um 20:31 Uhr
Hi Andreas

Danke für diesen Beitrag mit dem Inhaltsverzeichnis.
Leider erstellt es jedesmal eine neue Tabelle die dann Tabelle 1,2,3,4 etc genannt werden.

Ich möchte aber beim erneuten ausführen des Makros "nur" das erste Tabellenblatt "Inhaltsverzeichnis" aktualisieren

Vielen Dank im Voraus

Herby


Andreas Unkelbach Homepage am 22.7.2014 um 14:48 Uhr
Hallo Herby,

hier hatte ich im Kommentar vom 8.5.2014 um 13:47 Uhr einen entsprechenden Hinweis hinterlegt.

Inhaltsverzeichnis aktualisieren


das von mir eingesetzte Makro ist so angelegt, dass es durch die Zeilen:

Dim tbl As Worksheet
Dim intZeile As Integer
Set tbl = Worksheets.Add(before:=Worksheets(1))
intZeile = 2
ActiveSheet.Name = Worksheets(1).Name

auf jeden Fall ein neues Tabellenblatt anlegt, welches vor allen anderen Tabellenblätter angelegt wird.

Sofern aber das vorhandene Inhaltsverzeichnis aktualisiert werden soll könnte die Anweisung

Set tbl = Worksheets.Add(before:=Worksheets(1))

entfernt werden und direkt über

ActiveSheet.Name = Worksheets(1).Name

das erste Tabellenblatt in der Liste für die Erstellung des Inhaltsverzeichnises genutzt werden.


Evenutell würde dir hier ein zweites Makro zur Aktualisierung des vorhandenen Inhaltsverzeichnis hilfreich sein. Es sollte dabei jedoch beachtet werden, dass in jeden Fall die erste Tabelle der Arbeitsmappe überschrieben wird.

Viele Grüße
Andreas Unkelbach


Andreas Unkelbach Homepage am 22.7.2014 um 15:01 Uhr
@Daniela:

Vielen Dank für die Einladung zum Kaffee. Allerdings verschlägt es mich leider etwas selten in Richtung 48683, so dass ich wohl in absehbarer Zeit nicht zum Mailschreiben kommen werde ;-).

Dennoch freut es mich, dass das auch von mir als Arbeitserleichterung genutzte Makro in Abwandlung auch für andere Zwecke genutzt werden kann :-). Dieser gelebte Austausch ist auch einer der Hauptgründe für diese Seite, da ich diese ebenfalls oftmals als "allwissende Müllhalde" verwende.

Viele Grüße aus 35390
Andreas Unkelbach


Hasso am 27.7.2014 um 15:41 Uhr
Exzellent, super! Ich bedanke mich sehr herzlich für eine grossartige Unterstützung.
in wirklicher Dankbarkeit, Hasso


jan am 15.8.2014 um 21:51 Uhr
hi kann man


jan am 15.8.2014 um 21:52 Uhr
hi kann man auch sagen, dass 5 Tabellenblätter NICHT mit erscheinen sollen im Inhaltsverzeichns? Also die bLÄTTER "Admin", "A", "B", "C", und "D" sollen nicht mit ins Inhaltsverzeichnis.

Vielen Dank auch von mir für deine Hilfe


Manuel am 28.9.2014 um 01:35 Uhr
Hallo zusammen

Vielen Dank für die tollen Tricks.
Gibt es die Möglichkeit das Inhaltsverzeichnis mit weiteren Daten (Zellen) anzureichern. Z.B. Titel aus A1, Untertitel aus B3, Datum aus C5?
Hintergrund: Habe eine Excelmappe mit Kundenblätter und würde gerne auf der ersten Seite ein verlinktes Inhaltsverzeichnis , welches gefiltert werden und ich für weitere auswertungen verwenden kann.
Dabei wäre natürlich diese automatische generierung der absolute Luxus, ich bin jedoch zuwenig in der Materie im Moment.
Wäre super dankbar für einen kleinen Hilfeschuppser.
Beste Grüsse
Silvio


Andreas Unkelbach Homepage am 28.9.2014 um 08:13 Uhr
Hallo Manuel,

grundsätzlich könntest du das Makro so erweitern, dass nicht nur die Überschrift aus A2 sondern auch der Untertitel aus B3 und das Datum aus C5 genommen wird.

Sollte dieses untereinander geschrieben werden, würde sich dieses allerdings wiederholen und du hättest dieses mehrfach drin.

Eine bessere Lösung wäre wohl nach dem Hyperlink noch Untertitel und Datum auszugeben.

Hierzu würdest du das Makro wie folgt anpassen:



Sub Inhalt_mit_Überschrift_aus_a2()
'
' Inhalt_mit_Überschrift_aus_a2 Makro
' Erstellt ein Inhaltsverzeichnis aller Tabellenblaetter und verwendet als Überschrift die Zelle A2 eines jeden Tabellenblattes
'

'
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer
Set tbl = Worksheets.Add(before:=Worksheets(1))
intZeile = 2
ActiveSheet.Name = Worksheets(1).Name
Cells(1, 1).Value = "Überschrift"
Cells(1, 2).Value = "Link"
Cells(1, 1).Font.Bold = True
Cells(1, 2).Font.Bold = True


Cells(1, 3).Value = "Untertitel"
Cells(1, 4).Value = "Datum"
Cells(1, 3).Font.Bold = True
Cells(1, 4).Font.Bold = True



For intTab = 2 To ActiveWorkbook.Worksheets.Count
tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a2"
tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color
tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name
tbl.Cells(intZeile, 2).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
"'" & Worksheets(intTab).Name & "'!A2", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).Name


tbl.Cells(intZeile, 3).Value = "='" & Worksheets(intTab).Name & "'!b3"
tbl.Cells(intZeile, 3).Value = "='" & Worksheets(intTab).Name & "'!c5"



intZeile = intZeile + 1
Next intTab
Worksheets(1).Cells.EntireColumn.AutoFit
End Sub


Ich hoffe, dass so das Prinzip klar wird.

Viele Grüße
Andreas Unkelbach


Manuel am 1.10.2014 um 17:28 Uhr
Lieber Andreas Unkelbach

Vielen Dank für deine schnelle Hilfe. Nun denke ich bin ich auf dem richtigen Weg und kann mir das Inhaltsverzeichnis mit den nötigen informationen zusammenstellen.

Was mich jedoch noch etwas stört ist, dass bei jedem Ausführen des Makros ein neues Arbeitsblatt/ Inhaltsverzeichnis angelegt wird, bzw. werden muss.

Ideal wäre hier wenn sich das Inhaltsverzeichnis beim Anlegen neuer Blätter jeweils aktualisieren würde? Weisst zu ob dies möglich ist?

Ich würde eben Das Inhaltsverzeichnis gerne auf meinem ersten Arbeitsblatt einfügen und daraus dann auch gewisse statistische Auswertungen ziehen können?

Evtl. kann ich Dir auch das Dokument weitersenden, wenn dies zur Veranschaulichung hilft?

Vielen Dank und Lg
Manuel


Manuel am 6.10.2014 um 14:45 Uhr
Hallo Liebe Excel Gemeinde

Ich habe folgende Problemstellung.
In einer sehr unfamgreichen Arbeitsmappe würde ich gerne ein Inhaltsverzeichnis erstellen:

Anforderungen:

1. Auflisten mehrerer Daten aus den einzelnen Tabellenblätter (um auch statistische Auswertungen ziehen zu können)
2. Einfügen in vordefiniertes Arbeitsblatt (Kein neues Blatt erstellen)
3. Automatisches aktualisieren, wenn neue Arbeitsblätter hinzukommen

Zusätzlich:

4. Einfügen Zurück zu Inhaltsverzeichnis-Link in A59 jedes Tabellenblattes

Mein jetziger Stand:

1. konnte ich lösen
2,3,4 komme ich irgendwie nicht weiter.

Würde vielleicht jemand seinen Wissensschatz mit mir teilen und mir weiterhelfen?

Vielen Dank und beste Grüsse
Manuel

Sub Inhalt_mit_Überschrift_aus_Tabelle()
'
' Inhalt_mit_Überschrift_aus_Tabelle Makro
' Erstellt ein Inhaltsverzeichnis aller Tabellenblaetter und verwendet Überschriften aus der Tabelle
'

'
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer
Set tbl = Worksheets.Add(before:=Worksheets(1))
intZeile = 2
ActiveSheet.Name = Worksheets(1).Name

Cells(1, 1).Value = "Link"
Cells(1, 2).Value = "Land"
Cells(1, 3).Value = "Bundesland"
Cells(1, 4).Value = "Gruppennr."
Cells(1, 5).Value = "Filialnr."
Cells(1, 6).Value = "Name/Firmenname"
Cells(1, 7).Value = "Strasse/Nr."
Cells(1, 8).Value = "Ortschaft"
Cells(1, 9).Value = "PLZ"
Cells(1, 10).Value = "Letzter Besuch"
Cells(1, 11).Value = "Bestellt"
Cells(1, 12).Value = "Vorgestellt"
Cells(1, 13).Value = "Facing 1"
Cells(1, 14).Value = "Facing 2"
Cells(1, 15).Value = "Facing 3"
Cells(1, 16).Value = "Facing 4"

Cells(1, 1).Font.Bold = True
Cells(1, 2).Font.Bold = True
Cells(1, 3).Font.Bold = True
Cells(1, 4).Font.Bold = True
Cells(1, 5).Font.Bold = True
Cells(1, 6).Font.Bold = True
Cells(1, 7).Font.Bold = True
Cells(1, 8).Font.Bold = True
Cells(1, 9).Font.Bold = True
Cells(1, 10).Font.Bold = True
Cells(1, 11).Font.Bold = True
Cells(1, 12).Font.Bold = True
Cells(1, 13).Font.Bold = True
Cells(1, 14).Font.Bold = True
Cells(1, 15).Font.Bold = True
Cells(1, 16).Font.Bold = True

For intTab = 2 To ActiveWorkbook.Worksheets.Count

tbl.Cells(intZeile, 1).Value = Worksheets(intTab).Name
tbl.Cells(intZeile, 1).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 1), Address:="", SubAddress:= _
"'" & Worksheets(intTab).Name & "'!f11", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).Name

tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color

tbl.Cells(intZeile, 2).Value = "='" & Worksheets(intTab).Name & "'!b3"
tbl.Cells(intZeile, 3).Value = "='" & Worksheets(intTab).Name & "'!e3"
tbl.Cells(intZeile, 4).Value = "='" & Worksheets(intTab).Name & "'!j13"
tbl.Cells(intZeile, 5).Value = "='" & Worksheets(intTab).Name & "'!j8"
tbl.Cells(intZeile, 6).Value = "='" & Worksheets(intTab).Name & "'!c8"
tbl.Cells(intZeile, 7).Value = "='" & Worksheets(intTab).Name & "'!c11"
tbl.Cells(intZeile, 8).Value = "='" & Worksheets(intTab).Name & "'!c12"
tbl.Cells(intZeile, 9).Value = "='" & Worksheets(intTab).Name & "'!f12"
tbl.Cells(intZeile, 10).Value = "='" & Worksheets(intTab).Name & "'!k5"
tbl.Cells(intZeile, 11).Value = "='" & Worksheets(intTab).Name & "'!c39"
tbl.Cells(intZeile, 12).Value = "='" & Worksheets(intTab).Name & "'!c40"
tbl.Cells(intZeile, 13).Value = "='" & Worksheets(intTab).Name & "'!c41"
tbl.Cells(intZeile, 14).Value = "='" & Worksheets(intTab).Name & "'!c42"
tbl.Cells(intZeile, 15).Value = "='" & Worksheets(intTab).Name & "'!c43"
tbl.Cells(intZeile, 16).Value = "='" & Worksheets(intTab).Name & "'!c44"



intZeile = intZeile + 1
Next intTab
Worksheets(1).Cells.EntireColumn.AutoFit
End Sub


Andreas Unkelbach Homepage am 16.4.2015 um 10:01 Uhr
Sorry, der Kommentar scheint von mir übersehen worden zu sein.

Im ursprünglichen Makro wird immer das Tabellenblatt vor den ersten vorhandenen Tabellenblatt genommen.

Set tbl = Worksheets.Add(before:=Worksheets(1))

Natürlich ist es hier auch möglich direkt Worksheets(1) oder auch der Name des Tabellenblattes zu verwenden.

Hiervon würde ich jedoch dringend abraten, da dadurch versehentlich vorhandene Daten im jeweiligen Blatt überschrieben werden. Daher habe ich dieses Makro in der persönlichen Makroarbeitsmappe hinterlegt und kann nach Ausführung ggf. das falsche Inhaltsverzeichnis einfach löschen.

Viele Grüße
Andreas Unkelbach


Anonym am 29.4.2015 um 14:03 Uhr
Sehr hilfreich!


Lo Voelsen am 30.4.2015 um 15:30 Uhr
Hallo Andreas,

vielen Dank fürs Teilen dieser SEHR hilfreichen Tipps! Toll!

Ich habe eine Frage, da ich mich mit der Makro-Programmierung in Excel leider nicht auskenne. Ich würde gern (ich habe Dein Makro in der Version Überschrift und Link daneben genommen) neben der Spalte Link noch eine Spalte haben, in der aus den einzelnen folgenden Blättern immer der Wert angezeigt wird, der rechts neben dem Feld "Gesamt" steht. Dieses Feld ist allerdings in den Blättern immer in einer anderen Zelle.
Geht sowas?

beste Grüße aus Berlin
Lo


Andreas Unkelbach Homepage am 30.4.2015 um 15:39 Uhr
Hallo Lo,

wenn ich dich richtig verstanden habe, möchtest du hier eine Suchfunktion innerhalb des Makros einbauen, dass dir immer ein Feld Gesamt sucht und daneben den Wert ausgibt.

Hier müsste ich tatsächlich passen.

Persönlich würde ich es daher eher über einen Umweg lösen indem ich das Feld neben Gesamt einen Namen per Namensmanager zuweisen würde der sich aus den jeweiligen Tabellenblattnamen und Gesamt ergeben würde.

Nehmen wir an, dass du die Blätter Januar bis Dezember hast und hier an unterschiedlichen jeweils ein Feld gesamt sein soll.

Dann würde ich eine Formel neben der Spalte einfügen, die sich aus Tabellennamen und Gesamt über ein Namensfeld das entsprechende Feld eingibt. Hierzu musst du jedoch im Namensmanager eine entsprechende Vorarbeit leisten und hättest ggf. das Risiko, dass das entsprechende Feld nicht zugewiesen wäre und dadurch eine Fehlermeldung entsteht. Dieses könntest du allenfalls durch WENNFEHLER wieder ausbügeln.

Das ist jetzt nur eine grobe Überlegung, aber vielleicht hilft dir diese Vorgehensweise ja weiter.

Viele Grüße
Andreas Unkelbach


Ronnie am 27.10.2015 um 12:41 Uhr
Hallo, vielen Dank für diese Anleitung. Klappt alles wunderbar. Jedoch habe ich noch eine Frage inwieweit es möglich ist, die versteckten Tabellenreiter nicht mit aufführen zu lassen? Meine Kenntnisse in VBA sind eher bescheiden. Kennt jemand ne Lösung?

Vielen Dank im Voraus.

Grüße


Andreas Unkelbach Homepage am 27.10.2015 um 14:40 Uhr
Hallo Ronnie,

ich könnte mir allenfalls vorstellen, dass du über die Registerfarbe das ausgeblendete Blatt schneller in der Übersicht identifizieren kannst und die relevante Zeile dann löschen würdest... Denkbar ist es natürlich auch das Registerblatt weiß zu färben, so dass die Überschrift nicht lesbar ist...

Mir war bis zu deinen Kommentar auch gar nicht bewust, dass ich so immer bei offizielen Tabellen verfahre bzw. ausgeblendete Blätter (meist Vorjahresdaten) ebenfalls in der Inhaltsübersicht ausblende.

Dein anderer Kommentar ist leider eines Spamfilters zum Opfer gefallen. Ich trage diesen manuell noch eben ein.

Viele Grüße
Andreas


Ronnie (manuell nachgetragen) am 27.10.2015 um 14:56 Uhr
ERGÄNZUNG:

leider bekomme ich es nicht hin das tabellenblatt einfach nur zu aktualisieren (Inhaltsverzeichnis wurde erfolgreich angelegt.)Folgenden Code verwende ich nach Vorgabe dieser Seite:

Sub Inhaltsverzeichnis_aktualisieren()
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer

(hier habe ich die genannte Zeile gelöscht (Set tbl = Worksheets.Add(before:=Worksheets(1)) )
intZeile = 2

' eingefügtes Blatt "Inhaltsverzeichnis" nennen
Worksheets(1).name = "Inhaltsverzeichnis"
ActiveSheet.name = Worksheets(1).name
Cells(1, 1).Value = "Inhaltsverzeichnis"

For intTab = 2 To ActiveWorkbook.Worksheets.Count
tbl.Cells(intZeile, 1).Value = Worksheets(1).name

tbl.Cells(intZeile, 1).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 1), address:="", SubAddress:= _
"'" & Worksheets(intTab).name & "'!A1", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).name


intZeile = intZeile + 1
Next intTab

' Spaltenbreite fixieren
Worksheets(1).Cells.EntireColumn.AutoFit

End Sub



Wo hab ich den Fehler gemacht? Grüße





Hallo, ich schaffe es nicht einfach das Inhaltsverzeichnis zu aktualisieren, habe folgenden Code nach Anleitung erstellt:


Sub Inhaltsverzeichnis_aktualisieren()
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer

ActiveSheet.name = Worksheets(1).name
intZeile = 2

' eingefügtes Blatt "Inhaltsverzeichnis" nennen
Worksheets(1).name = "Inhaltsverzeichnis"
ActiveSheet.name = Worksheets(1).name
Cells(1, 1).Value = "Inhaltsverzeichnis"

For intTab = 2 To ActiveWorkbook.Worksheets.Count
tbl.Cells(intZeile, 1).Value = Worksheets(1).name

tbl.Cells(intZeile, 1).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 1), address:="", SubAddress:= _
"'" & Worksheets(intTab).name & "'!A1", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).name


intZeile = intZeile + 1
Next intTab

' Spaltenbreite fixieren
Worksheets(1).Cells.EntireColumn.AutoFit

End Sub

weiss jmd einen Rat?

Viele Grüße


Andreas Unkelbach Homepage am 27.10.2015 um 15:01 Uhr
So, nun sollten deine beiden Kommentare hier ebenfalls lesbar sein...sorry :-) Ich schaue mir heute Abend deine Frage etwas näher an und hoffe, dir zumindest einen kleinen Tipp auf den Weg geben zu können. Zwei KOmmentare oberhalb bin ich auf das Problem mit den ausgeblendeten Tabellenblättern eingegangen.

Viele Grüße
Andreas


Anonym am 19.1.2016 um 13:26 Uhr
Hat geklappt!


tanja am 8.6.2016 um 10:58 Uhr
Hallo, kann man das für uns die keine Ahnung von Makros auch erklären? Wo genau muss ich was eingeben? Ich habe Office 2015. Hilfe. Danke


Andreas Unkelbach Homepage am 8.6.2016 um 12:48 Uhr
Hallo Tanja,

die Erstellung eines Makros und Bearbeitung des dort hinterlegten VBA Code habe ich im Artikel Excel Umgang mit Makros und Visual Basic for Applications (VBA) ausführlicher erläutert. Dieses kann dann auch entsprechend mit oberen Makro genutzt werden.

Die elegantere Variante ist es natürlich das entsprechende Makro in einer Persönliche Makroarbeitsmappe zu hinterlegen, so dass dieses jederzeit aufgerufen werden kann.

Eine ausführlichere Beschreibung ist auch in diesen Artikel zu finden.

Viele Grüße
Andreas Unkelbach



Sarah am 4.4.2018 um 17:58 Uhr
Hallo Andreas,

zunächst vielen Dank für die ausführlichen Beschreibungen, das Erstellen eines individuellen Inhaltsverzeichnisses hat schon wunderbar geklappt!

Nun möchte ich gerne zusätzlich auf dem Inhaltsverzeichnis einen Eintrag "Stand" aus den einzelnen Arbeitsblättern hinterlegen. Für die ersten 10 Arbeitsblättern müsste hierfür der Wert aus der Zelle A2 eintragen werden, für alle übrigen die dahinter folgen wäre es die Zelle F8.

Die Arbeitsblätter 1-10 sind grundsätzlich leider nicht analog zu den restlichen Arbeitsblättern aufgebaut, sodass ich nicht auf dieselbe Zelle verweisen kann.

Schick wäre es außerdem wenn ich auf meinem Inhaltsverzeichnis eine Leerzeile
nach dem Arbeitsblatt 10 einfügen könnte.

Wie müsste ich meinen Code verändern?

Viele Grüße,
Sarah

------------------------------------------------------------------


Sub Inhaltsverzeichnis()
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer

Set tbl = Worksheets.Add(before:=Worksheets(1))
intZeile = 3

'Zellenüberschriften

ActiveSheet.Name = "Inhaltsverzeichnis"
Cells(1, 1).Value = "Arbeitsblatt"
Cells(1, 2).Value = "Geschoss"
Cells(1, 3).Value = "Raumnnummer"
Cells(1, 4).Value = "Stand"
Cells(1, 5).Value = "Bearbeiter"
Cells(1, 1).Font.Bold = True
Cells(1, 2).Font.Bold = True
Cells(1, 3).Font.Bold = True
Cells(1, 4).Font.Bold = True
Cells(1, 5).Font.Bold = True

For intTab = 2 To ActiveWorkbook.Worksheets.Count

'Setzen eines Hyperlinks auf Tabellenblatt

tbl.Cells(intZeile, 1).Value = Worksheets(intTab).Name

tbl.Cells(intZeile, 1).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 1), Address:="", SubAddress:= _
"'" & Worksheets(intTab).Name & "'!A1", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).Name

'Übernahme der Registerblattfarbe als Schriftfarbe

tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color

'Weitere Felder aus Tabellenblättern

tbl.Cells(intZeile, 2).Value = "='" & Worksheets(12).Name & "'!m8"
tbl.Cells(intZeile, 3).Value = "='" & Worksheets(12).Name & "'!m10"
tbl.Cells(intZeile, 4).Value = "='" & Worksheets(12).Name & "'!f8"
tbl.Cells(intZeile, 5).Value = "='" & Worksheets(12).Name & "'!f10"

intZeile = intZeile + 1
Next intTab

'Spaltenbreite fixieren

Worksheets(1).Cells.EntireColumn.AutoFit

End Sub


Andreas Unkelbach Homepage am 4.4.2018 um 18:20 Uhr
Hallo Sarah,

eine schöne Aufgabe. Vermutlich würde ich hier über die Formel INDIREKT einen Bezug aufbauen. Sofern die Blattnamen sich ändern erscheint mir hier das Coding etwas schwierig.

Ohne dies direkt testen zu können wäre dies ein möglicher Ansatz.

Ggg. werde ich mir dies am Wochenende noch einmal ansehen.

Ein Lösungsweg könnte das aufzeichnen als Makro und bearbeiten des VBA Code sein.


Schönen Abend
Andreas Unkelbach


Auch kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert; die angegebene E-Mail wird nicht veröffentlicht: beim Versenden als "Normaler Kommentar" ist die Angabe eines Namen erforderlich, gerne kann hier auch ein Pseudonyme oder anonyme Angaben gemacht werden (siehe auch Kommentare und Beiträge in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)

Name (sofern kein Name angegeben wird erscheint Anonym):



Ich nutze zum Schutz vor Spam-Kommentaren (reine Werbeeinträge) eine Wortliste, so dass diese Kommentare nicht veröffentlicht werden. Sollte ihr Kommentar nicht direkt veröffentlicht werden, kann dieses an einen entsprechenden Filter liegen.

Im Zweifel besteht auch immer die Möglichkeit eine Mail zu schreiben oder die sozialen Medien zu nutzen. Meine Kontaktdaten finden Sie auf »Über mich«. Ansonsten antworte ich tatsächlich sehr gerne auf Kommentare und freue mich auf einen spannenden Austausch.

Normaler Kommentar

Name:

E-Mail (wird nicht veröffentlicht und ist nicht erforderlich):

Homepage:

Kommentar:





* Amazon Partnerlink
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste