Handy Excel Tips

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

Popularity: 40%