List Sheet Tab Names with Excel VBA

Comments

1. Rick Rothstein (MVP - Excel) says

   March 16, 2016 at 1:48 pm

   Your readers of this article might find the blog article I posted here to be of some interest…

   http://www.excelfox.com/forum/f22/a-neat-go-to-sheet-selector-1830/

   Reply

2. Tony Calvario says

   March 8, 2014 at 8:15 pm

   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 says

     March 8, 2014 at 11:19 pm

     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

3. Melodie says

   July 4, 2013 at 5:33 am

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

   Melodie

   Reply

   • Philip Treacy says

     July 4, 2013 at 12:15 pm

     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

4. Aris Tab says

   April 30, 2013 at 5:06 pm

   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 says

     July 4, 2013 at 12:20 pm

     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

5. Suresh says

   February 3, 2013 at 2:14 am

   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 says

     February 3, 2013 at 9:08 pm

     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

6. Melissa says

   January 4, 2013 at 7:01 am

   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 says

     January 4, 2013 at 9:19 pm

     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

7. Jeneane says

   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.

   Reply

   • Philip Treacy says

     December 29, 2012 at 6:21 pm

     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

8. nayio says

   November 18, 2012 at 2:54 am

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

   Reply

   • Mynda Treacy says

     November 18, 2012 at 8:45 am

     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