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
👉 www.howtoexcel.org/courses/
DOWNLOAD the example workbook here
📖 www.howtoexcel.org/downloads/
SUBSCRIBE & get my 3 FREE eBooks
📧 www.howtoexcel.org/newsletter/
CONNECT with me on social
Facebook: / howtoexcelblog
Twitter: / howtoexcelblog
LinkedIn: / john-macdougall
Thanks for all your support!

[@HowToExcelBlog]

Check out my full courses and ebooks here 👉 www.howtoexcel.org/courses/

[@victoriaplatt3126]

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.

[@wayneedmondson1065]

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!

[@LaLLeR33]

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

[@ajaygaikwad5308]

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

[@HowToExcelBlog]

Ha, I wouldn't want to be doing that manually 😂

[@erikguzik8204]

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.

[@vitarathiel]

what if for office 2013 ?

[@akshaydalvi2317]

Thank you so much! This is great!

[@sarahalkahery1061]

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

[@leomessi8098]

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

[@BLUEPLANETJAZZ]

This is brilliant--thank you!!!!

[@halpwr]

you rocked it, thank you!

[@sabrinalimage166]

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!)

[@frenchtranslator]

Thank you for saving me thousands of hours.

[@palashmondal9088]

Thank you so much! This video is very helpful.

[@HowToExcelBlog]

You're welcome

[@Wizhka]

Gracias. Me es de gran utilidad.

[@EmmanuelOlaleye]

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

[@noblestride]

Thank so much!!

[@jadawson1]

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.

[@chiragpatel1112]

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?

[@Larwai]

Thanks, the VBA works for me.

[@HowToExcelBlog]

Great!

[@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 ?

[@alpesh9666]

thanks nice tutorial

[@tomronstone]

Sir, you have changed my life 🙏

[@HowToExcelBlog]

Ha, Excel is life changing 😂

[@shahbazraza8605]

Awesome

[@allabout1135]

WOW this was reall cool trick.

[@HowToExcelBlog]

Thanks Aleksandrs

[@khemrajrana7322]

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

[@terrydouglasjayasuriya5162]

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.

[@suatalturk3639]

great thank you much:)

[@HowToExcelBlog]

Welcome 👍

[@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?

[@nimrashabbir7001]

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

[@urjasanghvi3526]

Hey! Need you help I tried doing it but a dialogue Box is displayed which says This Operation has been cancelled due to restrictions in effects on this computer. please contact your system Administrator . Can you please guide me further

[@MelW999]

Thank you, I actually got it to work! Can you add a button on sheets to take you back to index?

[@HowToExcelBlog]

Use the HYPERLINK function for this.

[@sandyapujari5448]

For criss cross Hyperlink do we have to individually give link

[@HowToExcelBlog]

What does criss cross mean?

[@juniorurgjohannesbourg8096]

THANK YU

[@HowToExcelBlog]

👍

[@nikden76]

if I could leave a hundred of likes I'd do that!!! You literally saved me hours of life! Will go grab a drink now! ))

[@HowToExcelBlog]

Good to hear! If something is time consuming there's always a better way!

[@PALLAB58]

Thank you

[@HowToExcelBlog]

Welcome 👍

[@TSZ0111]

question regarding the second method, I did the same thing but the result returns #N/A , how do i fix it ?

[@jessehing-mather3196]

Is there a way to use the listed sheet names as a reference to get data from that sheet with formula? Say I want to get data from the sheet names Alabama without having to manually select it.

[@HowToExcelBlog]

Yes, use the INDIRECT function.

[@manikandanpalanisamy8815]

Sir When i save the file it show like that and i save on macro book format. if again open the file it will not showing

[@wayneedmondson1065]

Hi John.. just viewed this again. Inspired me to create a UDF as follows: Function ListSheetNames(IndexNo As Integer) As String Application.Volatile Dim col As New Collection Dim ws As Worksheet For Each ws In Worksheets col.Add ws.Name Next ws ListSheetNames = col(IndexNo) End Function Can use the above like the XML Macro 4, but it is more dynamic with Application.Volatile. If you add.. Private Sub Worksheet_Activate() ActiveSheet.Calculate End Sub ..to the subject Index sheet, then the results auto-update with any changes you might make.. add/delete sheets.. change sheet names.. move sheet positions, etc. I find it handy for making those sheet indexes or a table of contents that you then link with the HYPERLINK function, as you demonstrated, but that might get morphed and modified in the future. Anyway, thought I would share this. Thanks for the tips and the inspiration to create something new for myself. Thumbs up!

[@HowToExcelBlog]

I think I remember reading that UDF can also spill. You should try one that returns an array of the sheet names.

[@wayneedmondson1065]

@@HowToExcelBlog Good idea.. will give it a try. Thanks!

[@HowToExcelBlog]

Let me know how how it goes. Sounds like it might make a good video if you get it working.

[@jeremyh.1243]

@Wayne sounds like what I need. Do you have a template file you could share or a video presentation of what you mention? I like the auto update feature you talk about.

[@wayneedmondson1065]

@@jeremyh.1243 Hi J. Hdz. - see my new post (on 8/1/20) to John M. I added the code for a UDF that will spill all visible worksheet names and is volatile and so will react to changes. If you have Microsoft 365, it should work for you. I don't have a video, but maybe John will make one. You should be able to copy and paste the code into a module in your workbook and then save as macro enabled and it will work, assuming you have MS 365. I don't have a web site with downloadable files, but if you really need it, might be able to do a share to a OneDrive folder. Hope the post helps you out. Good luck!

[@ROHITKUMAR-cb9fs]

I am not able see properly of (getworkbook formula) could you please write here so i could see property

[@tutsecret499]

The list with the vba method does not have hyperlink to jump to the selected sheet?

[@ansuyadav2780]

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

[@gregoriusbayuaji7962]

Hi! Excuse me, why my get.workbook(1) only shows the first cell and my Replace function is not valid? What's wrong with it? Thank you

[@sohamsaha1422]

How can I list data from each sheet as well. Lets say I have listed all names, and adjacent to that, I wanna list specific cell from each work book. Please let me know

[@tetocaloscojones8229]

the download link does not work...no file is downloaded...no chance to do that ...I already signed up. How to do to download the file you are working on the video ? Thanks

[@noah1gulfbreeze]

Very nice...as always, on point and a clean presentation. Can power query provide a variation of the solution as well?

[@HowToExcelBlog]

I was planning on making the video 3 ways to list sheets, but it turns out you can't self-reference a workbook to get sheet names (only to get tables). Check out my blog on getting sheet names from a folder (current workbook can't be in the folder). www.howtoexcel.org/power-query/get-all-sheet-names-from-all-workbooks-in-a-folder/

[@brianxyz]

@@HowToExcelBlog I just tried doing this bringing in a folder and was able to get it work with the current workbook as part of the folder. After a bit of filtering I was able to load the file names into Excel.

[@HowToExcelBlog]

Hmm, I got an error. Maybe I'll try again tomorrow.

[@gururajchawan]

Agree! 😊

[@emiroviski]

is there a way to make this dynamic? i mean what if i wanted the list to update automatically after creating a new sheet?

[@HowToExcelBlog]

You have to manually recalculate the sheet.

[@rks3488]

List the sheet in one sheet and hyperlink that sheet was the thing i was searching...I was complete video..though i have a doubt...How can i name hyperlink to different name other than just GO TO SHEET ? Help will be appreciated

[@HowToExcelBlog]

Just replace GO TO SHEET with the text you want to appear!

[@gururajchawan]

Hello! Hyperlinking didn't worked for me. Can you please help?

[@Samer5746]

what if i want to list all sheets name that only lies between two specific sheet and dont show the rest of the sheets in the list??

[@dirkflanigan4511]

Hello, I hit subscribe Thank you, May you please send me the code? this is exactly what I need. Thanks again.

[@nogoogle6349]

Formula isn't working

[@meghawalia3285]

Can u please mention the formula over her so that we can directly write it down