Wie erstelle ich eine Liste aller Arbeitsblattnamen aus einer Arbeitsmappe?

Stellen Sie sich vor, Sie haben eine Arbeitsmappe mit mehreren Arbeitsblättern und möchten alle Blattnamen automatisch auflisten, versehen mit klickbaren Hyperlinks, um zu jedem entsprechenden Blatt zu navigieren. Dieses Tutorial bietet schnelle und effektive Methoden zum Generieren einer Liste von Arbeitsblattnamen in Excel.

Dynamische Liste aller Arbeitsblattnamen aus einer Arbeitsmappe mit Formeln erstellen

Liste aller Arbeitsblattnamen aus einer Arbeitsmappe mit Kutools für Excel erstellen

Liste aller Arbeitsblattnamen aus einer Arbeitsmappe mit VBA-Code erstellen

Dynamische Liste aller Arbeitsblattnamen aus einer Arbeitsmappe mit Formeln erstellen

In Excel können Sie einen Bereichsnamen definieren und dann eine Formel anwenden, um alle Blattnamen der aktuellen Arbeitsmappe aufzulisten. Gehen Sie dazu wie folgt vor:

1. Gehen Sie auf „Formeln“ > „Namen verwalten“, siehe Screenshot:

2. Klicken Sie im Dialogfeld „Namen verwalten“ auf die Schaltfläche „Neu“, siehe Screenshot:

3. Geben Sie im aufgepoppten Dialogfeld „Neuer Name“ einen Namen in das Textfeld „Name“ ein, und kopieren Sie dann die folgende Formel in das Textfeld „Bezieht sich auf“, siehe Screenshot:

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

4. Klicken Sie anschließend auf „OK“ > „Schließen“, um die Dialogfelder zu schließen. Gehen Sie nun zu einem Blatt, wo 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 ist „Sheetnames“ der Bereichsname, den Sie in Schritt 3 erstellt haben.

5. Ziehen Sie dann den Ausfüllkursor nach unten, bis leere Zellen angezeigt werden, und jetzt wurden alle Blattnamen der aktuellen Arbeitsmappe wie im folgenden Screenshot aufgelistet:

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

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

Hinweis: In der obigen Formel ist „A2“ die Zelle, die den Blattnamen enthält, und „A1“ ist die Zelle, in der sich die aktive Zelle befinden soll. Zum Beispiel, wenn Sie auf den Hyperlinktext klicken, wird die Zelle A1 des Blatts ausgewählt.

7. Jetzt, wenn Sie auf den Hyperlinktext klicken, gelangen Sie zu diesem Blatt, siehe folgende Demo:

Liste aller Arbeitsblattnamen aus einer Arbeitsmappe mit Kutools für Excel erstellen

Mit dem Hilfsprogramm „Verknüpfungstabelle erstellen“ des Drittanbieter-Add-ins „Kutools für Excel“ können Sie mit einem Klick eine Liste von Arbeitsblattnamen erstellen und diese mit Hyperlinks verknüpfen.

1. Klicken Sie auf „Kutools Plus“ > „Arbeitsblatt“ > „Verknüpfungstabelle erstellen“, siehe Screenshot:

2. Geben Sie im Dialogfeld „Verknüpfungstabelle erstellen“ die folgenden Einstellungen an:

(1.) Wählen Sie den gewünschten „Stil“ aus. Sie können die Arbeitsblattnamen mit Hyperlinks oder mit Makro-Schaltflächen erstellen. (2.) Geben Sie die Arbeitsblattnamen für den Blattindex ein. (3.) Legen Sie den Ort des Arbeitsblattindex fest. (4.) Geben Sie an, wie viele Spalten Sie im neuen Arbeitsblatt verwenden möchten, um die Arbeitsblattnamen anzuzeigen.

3. Nachdem Sie die Einstellungen abgeschlossen haben, klicken Sie auf „OK“. Alle Arbeitsblattnamen wurden mit Links in einem neuen Arbeitsblatt der aktuellen Arbeitsmappe aufgelistet. Siehe Screenshot:

Arbeitsblattnamen mit Hyperlinks	Arbeitsblattnamen mit Makro-Schaltflächen

Liste aller Arbeitsblattnamen aus einer Arbeitsmappe mit VBA-Code erstellen

1. Halten Sie die Tasten „ALT“ + „F11“ gedrückt, und es öffnet sich das Fenster „Microsoft Visual Basic for Applications“.

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

VBA: Liste aller Arbeitsblattnamen mit Hyperlinks in einem neuen Arbeitsblatt:

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 aus der aktiven Arbeitsmappe sind jetzt in einem neuen Arbeitsblatt namens „Index“ aufgelistet, mit Hyperlinks, die zu jedem jeweiligen Blatt führen. Siehe folgenden Screenshot:

Ähnliche Artikel