My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

List Sheet Tab Names with Excel VBA

You are here: Home / Excel VBA / List Sheet Tab Names with Excel VBA
list sheet tab names with vba
by

As I promised last week, today’s tutorial is a dead easy macro that will allow you to extract a list of your worksheet tab names.

You can then use this list to:

  • Create an index of your worksheets. Why not add Hyperlinks so you can quickly navigate your workbook.
  • Or use them to create references on the fly with the INDIRECT function and much more.

Here’s how:

  1. Add a command button to the worksheet. Go to the Developer tab of the ribbon > Insert > Command Button from the ActiveX Controls group.

    2.  
    Excel Sheet Name List Macro

    If you haven’t got the Developer Tab do the following:

    • Excel 2007 > Windows Button > Excel Options > Popular > Show Developer tab in the ribbon > OK.
    • Excel 2010 > File tab > Options > Customize Ribbon > on the right hand window (Customize the Ribbon) tick the Developer box > OK.

     

  2. Draw the button on the worksheet. To re-size it click and drag the borders.

    3.  

  3. Rename your command button. Right-click > CommandButton Object > Edit.

    4.  
    Excel Sheet Name List Macro

  4. Now, add Visual Basic code to the command button. Right-click > View Code:

    5.  
    Excel Sheet Name List Macro

  5. In the Visual Basic Editor (VBA), enter the following code between the Private Sub CommandButton1_Click()  statement and the End Sub statement:

    6.  

    Set NewSheet = Sheets.Add(Type:=xlWorksheet)

For i = 1 To Sheets.Count

    With NewSheet.Cells(i, 1)
        .NumberFormat = "@"
        .Value = CStr(Sheets(i).Name)
    End With

Next i

    So it looks like this:

     
    Excel Sheet Name List Macro

  6. On the File menu in the VBA editor, click Close and Return to Microsoft Excel.

    7.  
    Excel Sheet Name List Macro

  7. Exit Design Mode by clicking on the Design Mode button on the Developer tab of the ribbon.

Excel Sheet Name List Macro

Now you’re ready to click the command button and watch the magic as Excel inserts a new sheet in your workbook with all of your sheet names listed and ready for action!

If you want to save the macro in your workbook you need to save the workbook as a 'Macro Enabled Workbook'.

To do this go File > Save As > from the 'Save as type' list select 'Excel Macro-Enabled Workbook (*.xlsm)'.

list sheet tab names with vba

More Excel VBA Posts

automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA
Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image
List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists
Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar
Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA
Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba
Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject
Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List
Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Tag:

Reader Interactions

Comments

  1. jim

    I have this Subroutine in my PERSONAL.xlsb

    Sub SheetList()
    For i = 1 To Sheets.Count
    Selection.Cells(i, 1) = “=HYPERLINK(“”#'””&””” & CStr(Sheets(i).Name) & “””&””‘!A1″”,””” & CStr(Sheets(i).Name) & “””)”
    Next i
    End Sub

    I’m sure it could be done in a less horrific-looking way, but it does the job of producing a list of hyperlinks to all sheets in the current workbook
    (just beware that it will irreversibly overwrite any text below the active cell!)

    It did take a while to get all the quotes and ampersands right!

    L.E.:
    I see from earlier comments that Phil has already done something similar to this (but much more elegantly)

    jim

    Reply

  2. Jess

    Hi,
    I have tried the following VBA code to list active sheet, however, it promped errors msg:
    Compile error:
    Expected end sub

    Please advise

    Sub ListSh()
    For i = 1 To Sheets.Count
    With Cells(i, 1)
    .NumberFormat = “@”
    .Value = CStr(Sheets(i).Name)
    End With
    Next i
    End Sub

    Reply

    • Philip Treacy

      Hi Jess,

      There’s nothing wrong with that piece of code, it works fine for me. The error message you are getting must be related to another Sub.

      You can post on the forum and supply the whole workbook if you want us to look at it.

      Regards

      Phil

      Reply

  3. Tony Calvario

    This was very helpful, is it possible to mail me the same request that Jeneane December 28, 2012 at 8:20 am

    This is great, thanks! I have a workbook that is highly edited and tab names are subject to change often. Is there a way to edit the code so the tab names overwrite to the same place when I press the button? More preferred: do you know how to edit the code so the names are listed on the same tab in which I create the button and overwrite with each click? I appreciate any help you can offer. My email is tony@isretail.co.za

    Reply

    • Catalin Bombea

      Hi Tony,
      It’s easy to change the code, this will run on the active sheet, so be careful not to run it on other sheets:

      Sub ListSh()
For i = 1 To Sheets.Count
    With Cells(i, 1)
        .NumberFormat = "@"
        .Value = CStr(Sheets(i).Name)
    End With
Next i
End Sub
      Reply

  4. Melodie

    I need code that will list hidden sheets. Currently this code hits a snag when it happens upon a hidden sheet. Thanks!

    Melodie

    Reply

  5. Aris Tab

    I want that the list of sheet names appear on the same sheet where the button is located. could you please customize one..thank you

    Reply

    • Philip Treacy

      Hi Aris,

      This is what you need :

      
Sub listsheets()

Set FirstSheet = Sheets(1)

For i = 1 To Sheets.Count

    With FirstSheet.Cells(i, 1)
        .NumberFormat = "@"
        .Value = CStr(Sheets(i).Name)
    End With

Next i

End Sub

      Regards

      Phil

      Reply

  6. Suresh

    This is extremely helpful.But if there could be one more thing then it could be very much thankful to me.The thing is that – how the tab name can be changed/edited/rename without going to each & every tab.

    Reply

    • Carlo Estopia

      Hi Suresh,

      I have created a procedure for you and call it in your buttons: i.e. Call ListSheets and Call RenameSheets.
      This is quite not a snappy solution, but very basic. It lists first the Sheets in a workbook (ListSheets procedure); then
      It renames the sheets. I did this type of approach so the user can have a say as to what sheet one will rename.

      Here’s how it’s done:
      1) Add two buttons please see List Sheet Tab Names with Excel VBA
      2) You can copy the codes to each respective buttons without the procedure name.
      ie. Exclude the “Sub ListSheets” and “End Sub” part
      OR
      3) You may add a module (this will come in handy when you want to call the procedures in a different sheet for a reason or two)
      How? 

      1 ALT + F11 , In the VBE Window Click "Insert" menu, Choose Module (note: not Class Module).
        2 Paste the two Procedures in the Module
        3 Call ListSheets to Button 1 : Call ListSheets
               RenameSheets to Button 2 : Call RenameSheets
      Sub ListSheets()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim r As Long
    Set wb = ActiveWorkbook
    r = 2
    For Each ws In wb.Worksheets
        ActiveSheet.Range("A" & r).Value = ws.Name
        r = r + 1
    Next
End Sub
Sub RenameSheets()

    Dim ws As Worksheet
    Dim MyRange As Range
    Dim r As Long
    Set MyRange = Columns("A:A")
    r = Application.WorksheetFunction.CountA(MyRange)
    For r = 2 To Application.WorksheetFunction.CountA(MyRange) + 1
    
            If ActiveSheet.Range("B" & r).Value = "" Then
            Else
                Set ws = Worksheets(ActiveSheet.Range("A" & r).Value)
                ws.Name = ActiveSheet.Range("B" & r).Value
                ActiveSheet.Range("A" & r).Value = ActiveSheet.Range("B" & r).Value
            End If
    Next

End Sub

      Cheers

      Carlo

      Reply

  7. Melissa

    I love this tip, and it works great…but is there a way to make it automatically hyperlink each of those sheet name to the sheet it’s pulling from? I see you said “Why not add Hyperlinks so you can quickly navigate your workbook.”, but it didn’t mention what to add to the code to make it do that for me. Thanks!

    Reply

    • Philip Treacy

      Hi Melissa,

      To create the hyperlinks with VBA change this line :

       .Value = CStr(Sheets(i).Name)

      to this

       .Hyperlinks.Add Anchor:=Range("A" & i), Address:="", _
         SubAddress:=Sheets(i).Name & "!A1", TextToDisplay:=Sheets(i).Name

      so you’ll end up with this code in your sub :

      
Set NewSheet = Sheets.Add(Type:=xlWorksheet)

For i = 1 To Sheets.Count

    With NewSheet.Cells(i, 1)
        .NumberFormat = "@"
        .Hyperlinks.Add Anchor:=Range("A" & i), Address:="", _
         SubAddress:=Sheets(i).Name & "!A1", TextToDisplay:=Sheets(i).Name
    End With

Next i

      NOTE : After Address:=””, there is an underscore _ which tells VBA that this line of code is very long and continues on the next line.

      If you don’t want to list or create a hyperlink to the first sheet, change

      For i = 1

      to

      For i = 2

      Regards

      Phil

      Reply

  8. Jeneane

    This is great, thanks! I have a workbook that is highly edited and tab names are subject to change often. Is there a way to edit the code so the tab names overwrite to the same place when I press the button? More preferred: do you know how to edit the code so the names are listed on the same tab in which I create the button and overwrite with each click? I appreciate any help you can offer.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *