Wie erstellt man eine dynamische Liste von Arbeitsblattnamen in Excel?

Wenn Sie mehrere Blätter in einer Arbeitsmappe haben und eine dynamische Liste aller Blattnamen in einem neuen Blatt der Arbeitsmappe erstellen möchten, wie können Sie das tun? Dieses Tutorial stellt einige Tricks vor, um diese Aufgabe schnell in Excel zu erledigen.

Erstellen Sie eine dynamische Liste von Arbeitsblattnamen mit 'Namen definieren' und Formel

Erstellen Sie eine dynamische Liste von Arbeitsblattnamen mit VBA-Code

Erstellen Sie eine dynamische Liste von Arbeitsblattnamen mit Kutools für Excel

Anzeigen einer dynamischen Liste von Arbeitsblattnamen mit Kutools für Excel

Erstellen Sie eine dynamische Liste von Arbeitsblattnamen mit 'Namen definieren' und Formel

1. Wählen Sie eine Zelle in einem leeren Blatt aus, hier wähle ich A1, und klicken Sie dann auf Formeln > Namen definieren. Siehe Screenshot:

2. Dann im Neuer Name Dialogfeld, geben Sie Blätter in das Name Textfeld ein (Sie können es nach Bedarf ändern) und geben Sie diese Formel ein =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","") in das Textfeld 'Bezieht sich auf'. Siehe Screenshot:

3. Klicken Sie auf OK. Gehen Sie zur ausgewählten Zelle (A1) und geben Sie diese Formel ein =INDEX(Blätter,ZEILEN($A$1:$A1)) (A1 ist die Zelle, in die Sie diese Formel eingeben, "Blätter" ist der Name, den Sie in Schritt 2 definiert haben), und ziehen Sie den Autoausfüllkursor nach unten, bis #BEZUG! erscheint.

Tipp: Wenn Arbeitsblätter entfernt oder hinzugefügt wurden, müssen Sie zu A1 gehen und die Eingabetaste drücken und den Autoausfüllkursor erneut ziehen.

Erstellen Sie eine dynamische Liste von Arbeitsblattnamen mit VBA-Code

Wenn Sie eine dynamische Liste von Arbeitsblattnamen erstellen möchten, die mit jedem Blatt verknüpft sind, können Sie VBA-Code verwenden.

1. Erstellen Sie ein neues Arbeitsblatt und benennen Sie es in Index um. Siehe Screenshot:

2. Klicken Sie mit der rechten Maustaste auf den Blattnamen von Index, wählen Sie Code anzeigen aus dem Kontextmenü. Siehe Screenshot:

3. Kopieren Sie im Popup-Fenster den folgenden VBA-Code und fügen Sie ihn ein.

VBA: Dynamische Liste von Arbeitsblattnamen erstellen.

Private Sub Worksheet_Activate()
	'Updateby20150305
	Dim xSheet As Worksheet
	Dim xRow As Integer
	Dim calcState As Long
	Dim scrUpdateState As Long
	Application.ScreenUpdating = False
	xRow                       = 1
	With Me
		.Columns(1).ClearContents
		.Cells(1, 1) = "INDEX"
		.Cells(1, 1).Name = "Index"
	End With
	For Each xSheet In Application.Worksheets
		If xSheet.Name <> Me.Name Then
			xRow                     = xRow + 1
			With xSheet
				.Range("A1").Name = "Start_" & xSheet.Index
				.Hyperlinks.Add anchor: = .Range("A1"), Address: = "", _
				SubAddress:             = "Index", TextToDisplay: = "Back to Index"
			End With
			Me.Hyperlinks.Add anchor: = Me.Cells(xRow, 1), Address: = "", _
			SubAddress: = "Start_" & xSheet.Index, TextToDisplay: = xSheet.Name
		End If
	Next
	Application.ScreenUpdating = True
End Sub

4. Klicken Sie auf Ausführen oder F5 um den VBA-Code auszuführen, jetzt wurde eine dynamische Liste von Arbeitsblattnamen erstellt.

Tipp:

1. Wenn das Arbeitsblatt der Arbeitsmappe gelöscht oder eingefügt wird, ändert sich die Liste der Arbeitsblattnamen automatisch.

2. Sie können auf den Namen des Arbeitsblatts in der Namensliste klicken, um zum Blatt zu gelangen.

Mit diesen beiden Methoden sind sie nicht bequem genug, wenn Sie an einem neuen Dienstprogramm interessiert sind. Sie können die nächsten zwei Methoden ausprobieren, die diese Aufgabe einfacher machen könnten.

Erstellen Sie eine dynamische Liste von Arbeitsblattnamen mit Kutools für Excel

 Wenn Sie nur schnell alle Arbeitsblattnamen der Arbeitsmappe auflisten und sie mit den Originalblättern verknüpfen möchten, können Sie Kutools für Excel’s Verknüpfungstabelle erstellen verwenden.

Nach der kostenlosen Installation von Kutools für Excel gehen Sie bitte wie folgt vor:

1. Klicken Sie auf Kutools Plus > Arbeitsblatt > Verknüpfungstabelle erstellen. Siehe Screenshot:

2. Im Dialogfeld ' Verknüpfungstabelle erstellen':

 

(1) Aktivieren Sie den gewünschten Stil im Abschnitt Stil ;
(2) Benennen Sie das neue Indexblatt, indem Sie die Daten in das Feld Name der Verknüpfungstabelle eingeben;
(3) Geben Sie den Ort an, an dem Sie das hinzugefügte Indexblatt platzieren möchten, im Position in der Arbeitsmappe Liste;
(4) Wenn Sie die Blattnamen in einer einzigen Liste anzeigen möchten, wählen Sie 1 Zeile in der Anzahl der zu überspannenden Zeilen Liste.

3. Klicken Sie auf Ok. Jetzt sehen Sie, dass die Blattnamen aufgelistet sind.

Tipps:

1. Sie können auf den Namen des Arbeitsblatts klicken, um schnell zu seinem Originalblatt zu wechseln.

2. Die Liste oder die Blattnamen können sich nicht dynamisch mit dem Einfügen oder Löschen von Blättern ändern.

3. Tatsächlich können Sie auch eine Liste von Schaltflächen erstellen, um zu den entsprechenden Blättern zu verlinken, indem Sie einfach Schaltflächen und Makros erstellen im Dialogfeld aktivieren. Siehe Screenshot:

Klicken Sie hier, um mehr über 'Verknüpfungstabelle erstellen' zu erfahren.

  Erstellen Sie eine anklickbare Liste von Arbeitsblattnamen

Anzeigen einer dynamischen Liste von Arbeitsblattnamen mit Kutools für Excel

Wenn Sie Kutools für Excel verwenden, können Sie auch das Navigation-Dienstprogramm verwenden, um die verlinkbaren Arbeitsblattnamen in einem Bereich anzuzeigen.

Nach der kostenlosen Installation von Kutools für Excel gehen Sie bitte wie folgt vor:

1. Klicken Sie auf Kutools > Navigation. Klicken Sie auf Arbeitsmappe & Blatt um die Arbeitsmappe und das Arbeitsblatt anzuzeigen, und Sie können eine Arbeitsmappe auswählen, dann werden ihre Arbeitsblätter im Navigation Bereich angezeigt. Siehe Screenshot:
   

Tipp:

Wenn Arbeitsblätter gelöscht oder hinzugefügt werden, können Sie auf die Aktualisieren-Schaltfläche  im Navigationsbereichklicken, um die Arbeitsblattnamen zu aktualisieren.

Klicken Sie hier für weitere Informationen zur Navigation.

  Navigationsbereich -- Blätter auflisten