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:
- Add a command button to the worksheet. Go to the Developer tab of the ribbon > Insert > Command Button from the ActiveX Controls group.
- 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.
- Draw the button on the worksheet. To re-size it click and drag the borders.
- Rename your command button. Right-click > CommandButton Object > Edit.
- Now, add Visual Basic code to the command button. Right-click > View Code:
- In the Visual Basic Editor (VBA), enter the following code between the Private Sub CommandButton1_Click() statement and the End Sub statement:
- On the File menu in the VBA editor, click Close and Return to Microsoft Excel.
- Exit Design Mode by clicking on the Design Mode button on the Developer tab of the ribbon.
If you haven’t got the Developer Tab do the following:
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:
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)'.
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
Catalin Bombea
Thanks for sharing Jim!
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
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
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
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:
Melodie
I need code that will list hidden sheets. Currently this code hits a snag when it happens upon a hidden sheet. Thanks!
Melodie
Philip Treacy
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
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
Philip Treacy
Hi Aris,
This is what you need :
Regards
Phil
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.
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?
Cheers
Carlo
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!
Philip Treacy
Hi Melissa,
To create the hyperlinks with VBA change this line :
to this
so you’ll end up with this code in your sub :
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
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.
Philip Treacy
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
nayio
Thanks for this. how to do the same thing in the excel 2008 version (for MAC) please?
Mynda Treacy
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.