List All Sheet Names In An Excel Workbook With & Without VBA

In this video, we take a look at how to generate a list of sheet names in an Excel workbook with and without VBA.

VBA method code:

Sub ListAllSheets()

Dim ws As Worksheet
Dim Counter As Integer

Counter = 0

For Each ws In ActiveWorkbook.Worksheets
ActiveCell.Offset(Counter, 0).Value = ws.Name
Counter = Counter + 1
Next ws

End Sub

XML Macro 4 method formula:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") 👇

Check out my full courses and ebooks here

DOWNLOAD the example workbook here

SUBSCRIBE & get my 3 FREE eBooks

CONNECT with me on social

Thanks for all your support!

[HowToExcelBlog]

Check out my full courses and ebooks here

[victoriaplatt]

Thank you for this video. It worked brilliantly and instantly made a list of (52) sheet names which was very useful. Thank you. Much appreciated.

[wayneedmondson]

Hi John.. thanks for the tips. Was familiar with For Each method in VBA. Thanks for the tip on the XML Macro 4 method and hyperlink bonus.. very helpful. Like your style.. short.. sweet.. to the point. Thumbs up!

[LaLLeR]

Thank you! You saved me a lot of time :) Great video! Easy to use! Thanks again!

[leomessi]

Thanks for the very simple explanation. Very helpful in saving time

[ajaygaikwad]

You just came out as a life saver with this video.. I work on a file which has 300sheets and I was doing this manually so far.

Thank you very much

[sarahalkahery]

Thank you! I needed to do this for work and it was going to take me hours

[palashmondal]

Thank you so much! This video is very helpful.

[sabrinalimage]

Thank you for this video! I was wondering...Is there a way to use this method (non-VBA), but only return sheet names under a specific condition? For example, I would like to return a list of sheet names but only if the conditional formatting on that sheet turns yellow. If not then my list would remain blank (hope that makes sense!)

[EmmanuelOlaleye]

Great video. Helped me a lot but how do I handle it when i rename one of the older sheets.?

[frenchtranslator]

Thank you for saving me thousands of hours.

[erikguzik]

Very cool trick, I've been looking for this idea for a long time. Update -- for those who now have office 365. To update what he put in Col(B) put in this formula =SEQUENCE((SHEETS())). This will give you a list from 1 to Number of sheets you have.

[chiragpatel]

Hi john great video, i wanted to know what is in that A1, if I want to have the value of that cell A1 displayed in that cell? how may I display value?

[parmeshwarcctv]

Non VBA Trick was new for me, thanks for sharing John, You explained Hyperlink function too, I need your help in Hyperlink....
One of my project have drop down and according selection there is different web link create by hyperlink, but first link is work fine, but on change drop down selection hyperlink function not updating, could you guide me ?

[jadawson]

Any ideas on how to get this to auto refresh or worst case include a button. Data refresh and auto calculate doesn't seem to update new sheets added. If I click on index formula and hit enter after a sheet is added it does update.

[terrydouglasjayasuriya]

Great video, and thanks. I would like to provide a description of the listed sheets. Upon adding another new sheet, I would like the corresponding descriptions of the sheets to also shift. Pls kindly adv. Thanks in advance.

[khemrajrana]

Great sir
& You upload video please provide excel sheet in descroption

[TheAlangham]

I have a sheet named "cover" and another named "data" that i want to exclude from the list. How would i go about that?

[nimrashabbir]

how do we create a link to the list created by macro?

[ansuyadav]

Can anyone explain me the formula for second trik....