list all the sheets of my workbook onto an excel sheet

[rjmdc]

hi
how can i create code to list by name all the worksheets in a workbook onto an excel sheet in this or another workbook
thanks

 

[alansidman]

List All Sheets with Excel VBA — Excel Dashboards VBA

Creating worksheet tab names in a distinct and uneatable list is a tedious task. Using Excel VBA is the answer to this problem.

www.thesmallman.com

 

[rjmdc]

hi
are you suggesting Power query?

 

[alansidman]

You could but I have not suggested that. Did you look at Smallman's link? Are you interested in a PQ solution?

 

[rjmdc]

hi
i missed that
i just found this and adapted and it worked great

Sub ListAllSheetNames()
Dim ws As Worksheet
Dim i As Integer
With ThisWorkbook.Worksheets("SheetNames")
.Range("A:A").ClearContents
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
.Range("A" & i) = ws.Name
Next ws
End With

thanks anyway