Wie erstellt man eine Liste aller Arbeitsblattnamen aus einer Arbeitsmappe?

Stellen Sie sich vor, Sie haben eine Arbeitsmappe mit mehreren Arbeitsblättern und möchten automatisch eine Liste aller Blattnamen erstellen – inklusive anklickbarer Hyperlinks, um direkt zu den jeweiligen Blättern zu navigieren. Dieses Tutorial zeigt Ihnen schnelle und effektive Methoden, um in Excel eine vollständige Liste aller Arbeitsblattnamen zu generieren.

Erhalten Sie dynamisch eine Liste aller Arbeitsblattname aus einer Arbeitsmappe mithilfe von Formeln

Erhalten Sie eine Liste aller Arbeitsblattname aus einer Arbeitsmappe mit Kutools für Excel

Erhalten Sie eine Liste aller Arbeitsblattname aus einer Arbeitsmappe mit VBA-Code

Erhalten Sie dynamisch eine Liste aller Arbeitsblattname aus einer Arbeitsmappe mithilfe von Formeln

In Excel können Sie einen Zellennamen definieren und anschließend eine Formel verwenden, um alle Blattnamen aus der aktuellen Arbeitsmappe aufzulisten. Gehen Sie dazu wie folgt vor:

1. Klicken Sie auf „Formel“ > „Namens-Manager“ – siehe Screenshot:

2. Klicken Sie im Dialogfeld „Namens-Manager“ auf die Schaltfläche „Neu“ – siehe Screenshot:

3. Geben Sie im erscheinenden Dialogfeld „Neuer Name“ einen Namen in das Textfeld „Name“ ein und fügen Sie anschließend die folgende Formel in das Textfeld „Bezieht sich auf“ ein (siehe Screenshot):

=GET.WORKBOOK(1)&T(NOW())

4. Klicken Sie anschließend auf „OK“ > „Schließen“, um die Dialogfelder zu schließen. Wechseln Sie nun zu dem Blatt, in dem Sie alle Blattnamen auflisten möchten, und geben Sie die folgende Formel in eine leere Zelle ein:

=IFERROR(INDEX(MID(Sheetnames,FIND("]",Sheetnames)+1,255),ROWS($A$2:A2)),"")

Hinweis: In der obigen Formel steht „Sheetnames“ für den Zellennamen, den Sie in Schritt 3 erstellt haben.

5. Ziehen Sie anschließend den Ausfüllknauf nach unten, bis leere Zellen erscheinen. Nun sind alle Blattnamen der aktuellen Arbeitsmappe wie im folgenden Screenshot dargestellt aufgelistet:

6. Falls Sie für jedes Arbeitsblatt einen Hyperlink erstellen möchten, verwenden Sie bitte die folgende Formel:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

Hinweis: In der obigen Formel steht „A2“ für die Zelle, die den Blattnamen enthält, und „A1“ für die Zelle, in der sich der aktive Cursor positionieren soll. Wenn Sie beispielsweise auf den Hyperlink klicken, springt Excel direkt zur Zelle A1 des entsprechenden Arbeitsblatts.

7. Wenn Sie nun auf den Hyperlinktext klicken, gelangen Sie direkt zu diesem Blatt – siehe folgende Demo:

Erhalten Sie eine Liste aller Arbeitsblattname aus einer Arbeitsmappe mit Kutools für Excel

Mit dem Tool „Verknüpfungstabelle erstellen“ des Drittanbieter-Add-Ins „Kutools für Excel“ erstellen Sie mit einem Klick eine Liste aller Arbeitsblattnamen und verknüpfen sie direkt mit Hyperlinks zu den jeweiligen Arbeitsblättern.

1. Klicken Sie auf „KUTOOLS PLUS“ > „Arbeitsblatt“ > „Verknüpfungstabelle erstellen“ – siehe Screenshot:

2. Legen Sie im Dialogfeld „Verknüpfungstabelle erstellen“ die folgenden Einstellungen fest:

(1.) Wählen Sie den „Stil“, der Ihnen gefällt. Sie können den Arbeitsblattnamen mithilfe von Hyperlinks oder Makroschaltflächen erstellen. (2.) Geben Sie den Namen des Arbeitsblatts für das Arbeitsblattverzeichnis ein. (3.) Geben Sie den Speicherort für das Arbeitsblattverzeichnis an. (4.) Geben Sie an, wie viele Spalten Sie im neuen Arbeitsblatt zur Anzeige des Arbeitsblattnamens verwenden möchten.

3. Nach Abschluss der Einstellungen klicken Sie auf „OK“. Alle Arbeitsblattnamen wurden nun mit Links in einem neuen Arbeitsblatt der aktuellen Arbeitsmappe aufgelistet. Siehe Screenshot:

Arbeitsblattname mit Hyperlinks	Arbeitsblattname mit Makroschaltflächen

Erhalten Sie eine Liste aller Arbeitsblattname aus einer Arbeitsmappe mit VBA-Code

1. Drücken Sie gleichzeitig die Tasten „ALT“ + „F11“, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Klicken Sie auf „Einfügen“ > „Modul“ und fügen Sie das folgende Makro in das Modulfenster ein.

VBA: Listet alle Arbeitsblattname mit Hyperlinks in einem Neues Arbeitsblatt auf:

Sub CreateIndex()
'updateby Extendoffice
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3. Drücken Sie die Taste „F5“, um dieses Makro auszuführen. Alle Arbeitsblattnamen der aktuellen Arbeitsmappe werden nun in einem neuen Arbeitsblatt namens „Index“ aufgelistet – versehen mit Hyperlinks, die zu den jeweiligen Blättern führen. Siehe folgender Screenshot:

Verwandte Artikel