List Worksheet Names

Macros April 14th, 2008

This article shows how to generate a list of all the worksheet names on a worksheet.

To list tab names in excel, we can use the following macro:

Sub ListWorkSheetNames()

Dim Sheetnames
Sheetnames = Sheets.Count
Sheets.Add
ActiveSheet.Name = “SheetList”
Sheets(”SheetList”).Move after:=Sheets(Sheetnames + 1)

For i = 1 To Sheetnames
Range(”A” & i) = Sheets(i).Name
Next i

End Sub

The above macro would add a new worksheet “SheetList” and will list all the tab names there. If you don’t wish to add this new tab but would like to list the worksheet names in the active worksheet then you can shorten the above macro:

Sub ListWorkSheetNames()

For i = 1 To Sheets.Count
Range(”A” & i) = Sheets(i).Name
Next i

End Sub

Reader's Comments

» Accessing other worksheets in a workbook faster » Handy Excel Tips | April 29th, 2008 at 10:30 pm
[...] List of all tabs in a workbook [...]
Jo | February 11th, 2009 at 7:26 pm
What version of Excel is this for? Won’t work on 2003 (which is what I have at work) and this is exactly what I was looking for!
Jo | February 11th, 2009 at 7:52 pm
I got it to work!
Shikha | February 14th, 2009 at 4:43 am
Hi Jo,
Nice to know that you figured it out, it is the quotes (”) which are getting missed in my blogging software which need to be replaced back to quotes.
Regards
Shikha
Trintiy | May 10th, 2009 at 3:59 pm
Excellent macro, thanks!
Lee | June 29th, 2009 at 4:24 am
Excellent macro. Just what I have been looking for
judgepax | December 28th, 2009 at 4:48 pm
Thank you for the wonderful macro! No one else seemed to know how to do this, so you just saved me HOURS!!
Nirmala | January 17th, 2010 at 4:37 am
Good marco. Something that I was trying to figure out for a long time. Thanks. It helped me a lot.. although I had to tweak it a bit.