List Sheet Tab Names with Excel VBA

19 thoughts on “List Sheet Tab Names with Excel VBA”

1. 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

   • Thanks for sharing Jim!

     Reply

2. 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

   • 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. 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 [email protected]

   Reply

   • 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. I need code that will list hidden sheets. Currently this code hits a snag when it happens upon a hidden sheet. Thanks!

   Melodie

   Reply

   • Hi Melodie,

     The code works fine for me with hidden sheets.

     If you want to send me your workbook I can look at it for you.

     Please raise a Help Desk ticket here https://www.myonlinetraininghub.com/help-desk and supply your workbook.

     Regards

     Phil

     Reply

5. 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

   • 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. 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

   • 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. 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

   • 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. 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

   • Hi Jeneane,

     I’ll email you directly and you can then send me your workbook. I’ll have a look at the code and see what I can do for you.

     Cheers

     Phil

     Reply

9. Thanks for this. how to do the same thing in the excel 2008 version (for MAC) please?

   Reply

   • Hi Nayio,

     Sorry, I don’t know how you do the same on a Mac. I suggest you find an Excel for Mac forum and ask the question on there.

     Kind regards,

     Mynda.

     Reply