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
Share ThisPopularity: 48%

[...] List of all tabs in a workbook [...]
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!
I got it to work!
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
Excellent macro, thanks!
Excellent macro. Just what I have been looking for
Thank you for the wonderful macro! No one else seemed to know how to do this, so you just saved me HOURS!!
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.