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.
Vindra | April 16th, 2010 at 2:00 pm
Thank you for this code. I took about 15 mins trying to figure out what was wrong with it though.

The problem is the quotes as Shikha said, the quotes copies and pastes into the VBA Editor as angular quotes, but all the occurrences MUST be changed to straight double quotes, otherwise you would get some very strange error each time!
Michael | August 19th, 2010 at 6:25 am
Brilliant!

Thanks very much :-)))
Ronnie M | August 30th, 2010 at 11:57 pm
Freakin’ awesome! Thank you - just what I was looking for.

Now to play with it to get the list to update if a new sheet is added….
CCox | November 4th, 2010 at 6:10 pm
Absolutely beautiful…saved me so much time. The key to make it work was the quote marks. Fixed that and worked perfectly. Thank you!
Nik | November 19th, 2010 at 2:02 am
Hi… yes the tool is really cool.
I been badly needed this for sometime. and the search pointed to your site SPORT ON.
THANKS FOR SHARING.
Anne | December 10th, 2010 at 7:36 pm
Thanks Shikha! Very useful!
K | December 22nd, 2010 at 10:02 am
Exactly what I was looking for. Thank you!!!