How To Create A Dynamic List Of Worksheet Names In Excel?

Excel is a powerful tool that allows you to organize and analyze data in a structured manner. One of the most common tasks that users perform in Excel is managing multiple worksheets within a workbook. As the number of worksheets in a workbook increases, it can become increasingly difficult to keep track of them all. In order to streamline your workflow and make your Excel experience more efficient, you can create a dynamic list of worksheet names that can be easily updated and referenced. This tutorial will guide you through the process of creating a dynamic list of worksheet names in Excel using a combination of formulas and built-in features. By the end of this tutorial, you will have a better understanding of how to manage and organize multiple worksheets in Excel using dynamic naming conventions.

Create A Dynamic List Of Worksheet Names

Here we can complete the task simply by inserting the VBA code into the sheet. So let us see a simple process to know how you can create a dynamic list of worksheet names in Excel.

Step 1

Consider any Excel sheet. First, right-click on the sheet name and select View code to open the VBA application, then copy the below-mentioned code into the text box as shown below.

Right click > View Code > Copy Code


Private Sub Worksheet_Activate()
   Dim xSheet As Worksheet
   Dim xRow As Integer
   Dim calcState As Long
   Dim scrUpdateState As Long
   Application.ScreenUpdating = False
   xRow = 1
   With Me
      .Cells(1, 1) = "Names"
      .Cells(1, 1).Name = "Names"
   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 Names"
            End With
            Me.Hyperlinks.Add anchor:=Me.Cells(xRow, 1), Address:="", _
            SubAddress:="Start_" & xSheet.Index, TextToDisplay:=xSheet.Name
      End If
   Application.ScreenUpdating = True
End Sub

Step 2

Now click F5 to run the code, and all the sheet names will now be listed on the sheet.


In this tutorial, we have used a simple example to demonstrate how you can create a dynamic list of worksheet names in Excel to highlight particular sets of data.

Updated on: 13-Jul-2023


Kickstart Your Career

Get certified by completing the course

Get Started