If you have an Excel workbook that has hundreds of worksheets, and now you want to get a list of all the worksheet names, you can refer to this article. Here we will share 3 simple methods with you.
Sometimes, you may be required to generate a list of all worksheet names in an Excel workbook. If there are only few sheets, you can just use the Method 1 to list the sheet names manually. However, in the case that the Excel workbook contains a great number of worksheets, you had better use the latter 2 methods, which are much more efficient.
Method 1: Get List Manually
- First off, open the specific Excel workbook.
- Then, double click on a sheet’s name in sheet list at the bottom.
- Next, press “Ctrl + C” to copy the name.
- Later, create a text file.
- Then, press “Ctrl + V” to paste the sheet name.
- Now, in this way, you can copy each sheet’s name to the text file one by one.
Method 2: List with Formula
- At the outset, turn to “Formulas” tab and click the “Name Manager” button.
- Next, in popup window, click “New”.
- In the subsequent dialog box, enter “ListSheets” in the “Name” field.
- Later, in the “Refers to” field, input the following formula:
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
- After that, click “OK” and “Close” to save this formula.
- Next, create a new worksheet in the current workbook.
- Then, enter “1” in Cell A1 and “2” in Cell A2.
- Afterwards, select the two cells and drag them down to input 2,3,4,5, etc. in Column A.
- Later, put the following formula in Cell B1.
=INDEX(ListSheets,A1)
- At once, the first sheet name will be input in Cell B1.
- Finally, just copy the formula down until you see the “#REF!” error.
Method 3: List via Excel VBA
- For a start, trigger Excel VBA editor according to “How to Run VBA Code in Your Excel“.
- Then, put the following code into a module or project.
Sub ListSheetNamesInNewWorkbook()
Dim objNewWorkbook As Workbook
Dim objNewWorksheet As Worksheet
Set objNewWorkbook = Excel.Application.Workbooks.Add
Set objNewWorksheet = objNewWorkbook.Sheets(1)
For i = 1 To ThisWorkbook.Sheets.Count
objNewWorksheet.Cells(i, 1) = i
objNewWorksheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
Next i
With objNewWorksheet
.Rows(1).Insert
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Font.Bold = True
.Cells(1, 2) = "NAME"
.Cells(1, 2).Font.Bold = True
.Columns("A:B").AutoFit
End With
End Sub
- Later, press “F5” to run this macro right now.
- At once, a new Excel workbook will show up, in which you can see the list of worksheet names of the source Excel workbook.
Comparison
| Advantages | Disadvantages | |
| Method 1 | Easy to operate | Too troublesome if there are a lot of worksheets |
| Method 2 | Easy to operate | Demands you to type the index first |
| Method 3 | Quick and convenient | Users should beware of the external malicious macros |
| Easy even for VBA newbies |
Excel Gets Corrupted
MS Excel is known to crash from time to time, thereby damaging the current files on saving. Therefore, it’s highly recommended to get hold of an external powerful Excel repair tool, such as DataNumen Outlook Repair. It’s because that self-recovery feature in Excel is proven to fail frequently.
Author Introduction:
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql fix and outlook repair software products. For more information visit www.datanumen.com
Hello,
If I have multiple sheets in my workbook, all with the same naming structure but different names & I need to delete the same 2 words from every sheet name on the tab, is there a way?
In my example below the ‘5 numbers’ are a random combo of 5 digits & are different for each tab.
The words ‘TRAINING CAMP’ are static & the same for all tabs.
The ‘Camp Name’ is a random name & different for each tab.
CURRENT TAB NAME STRUCTURE:
5 Numbers – TRAINING CAMP – Location
CURRENT TAB NAME ACTUAL EXAMPLE:
00001 – TRAINING CAMP – Dover
00010 – TRAINING CAMP – Cabot
00100 – TRAINING CAMP – Dallas
DESIRED TAB NAMES EXAMPLE:
1 – Dover
10 – Cabot
100 – Dallas
To do a recalculation automatically, that is when you add a new sheet, Use worksheet active event on that particular sheet.
Private sub worksheet_activate()
Application.screenupdating=False
Application.CalculateFullRebuild
Application.Screenupdating = True
End Sub
you saved me a lot of time, cheers!
Nevím, ale mně metoda č. 2 nefunguje – píše, že ve vzorci je chyba.
Method 2 worked great. So easy and fast.
If you want to see the names of the tab and not to store it in a file follow the steps mentioned below:
Step 1: Keep the mouse on either the “left tab shift” icon (represented by symbol).
Step 2: Press “Ctrl” buttion and right click mouse button.
Step 3: You will see the all the “tab names” with a popup menu heading “Activate”
If you want to just view the names of the tabs just follow the 3 simple steps mentioned below:
Step 1: Keep the mouse on either the “left tab shift” icon (represented by symbol).
Step 2: Press “Ctrl” buttion and right click mouse button.
Step 3: You will see the all the “tab names” with a popup menu heading “Activate”
Worked like a charm. Thank you.
Wow, amazing blog structure! How long have you been blogging for?
you make running a blog look easy. The whole look of your website is great, let alone the content!
You can see similar here e-commerce
I have thought and have removed the message
For method #2, using the ListSheets macro (=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)), did anyone find a way to force re-calculation? I can change a sheet name, but until I clear the cell contents and re-enter the =INDEX(ListSheets,[RefCell]) formula, the old sheet name stays in the result.
I did not see a reply to others who posted questions, but I did encounter the #BLOCKED! error after closing Excel and re-opening the file with this macro. I needed to make the file directory where it is stored a Trusted Location.
Extraordinary!!!!
Worked awesome and easy with the formula method. Amazing. Thank you!
Love you for this!
We would like to thank you once more for the wonderful ideas you offered Janet when preparing her own post-graduate research
plus, most importantly, regarding providing all the ideas in a blog post.
Provided that we had been aware of your website a year ago, we might have been rescued
from the useless measures we were employing.
Thank you very much. toys for adults
We are a group of volunteers and opening a new scheme in our community.
Your website offered us with valuable info to work on. You’ve done a formidable job
and our whole community will be grateful to you.
donate for ukraine
Excellent, what a weblog it is! This web site gives helpful data to
us, keep it up. donate for ukraine
Please work krna sikha dain
Thank you for this. To save a step, I used the function:
=INDEX(ListSheets,ROW(A1))
and copied it down. That way, I don’t need a Column to hold the numbers 1 to n
Method 3 works like a charm. Does anyone know how to change the VBA code to create a worksheet within the existing workbook to show the list of worksheet names?
I love your blog.. very nice colors & theme. Did you make this website yourself or did you hire someone to do it for you? Plz reply as I’m looking to design my own blog and would like to find out where u got this from. thanks a lot
大変参考になりました、ありがとう。
WHAT IS MY FILE NAME IS VERY LONG? SAY IT IS NUMERIC AND 24 DIGIT LONG.. i FOUND BY USING THIS FORMULA ONLY FIRST 12 DIGITS OF THE WORKSHEETS GETS CAPTURED
Thank you. Method 2 was exactly what I needed 🙂
If I change a sheetname, i doesn’t update and cells referenced to it get an #REF! error. How to solve without macro? Thanks.
o.web20.services
Thank you very much – exactly what I needed.
Thank you so much!!! Extremely helpful
Thanks a lot!
With the new O365 functions, you can directly use =TRANSPOSE(ListSheets).
If you also want/need the sheet numbers, =SEQUENCE(COLUMNS(ListSheets)) is even dynamic.
thank you, method 3 saved me a few hours of ctr-C/ctr-V
Thank you so much for sharing these three options. I disagree with the user who referred to the post as “idiotic”. It’s very easy to criticize, but takes real effort to help others. Thank you for your help!
this is idiotic–just make a freaking PDF from the excel file–you’ll get the names and a PDF of each.
Method two worked like a charm, thanks
How do I get it to make/update a tab in the current workbook instead of creating a new workbook?
Awesome! Method two worked great. Instructions were clear & screenshots were helpful. Doesn’t get better.
I also get the #BLOCKED! error message for option 2 and I can’t find a way to ‘unblock’ 🙁
Thanks, Saved me a lot of work
Method 2 does not work in Excel 2021!
Just a comment method 2 doesnt require a list first if you replace the =INDEX(ListSheets,A1) with =INDEX(ListSheets,Row(A1)).
Method 2 also requires you to save as macro enabled workbook as it uses Excel 4.0 legacy function
Really appreciated. So swift to use the VBA from ‘low knowledge’!
Thanks, how do I make the results output in my current file on the tab “Tab Index” which already exists, better yet into a table?
That was awesome!! Thanks for the VBA Code!!!!!! Saved me like an hour of mind numbing work!
I get an error #BLOCKED! when I try the method 2 (list with formula). I want to use the sheet name within my workbook, so the VBA method is no help.
Hi, List via Excel VBA works great, plz suggest what to change the code to get it start in row4, column B (Index in B4, Name in C4)
Thank you! This saved so much time for me, I really appreciate your sharing this with all!
BTW, is there a way to print just the visible worksheets (and not the hidden ones)? I know i’m asking for a lot here, but that might make the formula even better 🙂
Congratulations and thank you so much!!!
Thank you for that macro! It worked like a charm
This is amazing. Thanks so much for post! You just saved me hours of work.