This post will guide you how to **get a list of all worksheet names** in an excel workbook. How do I List the Sheet names with Formula in Excel. How to generate a list of all sheet tab names using Excel VBA Code.

Assuming that you have a workbook that has hundreds of worksheets and you want to get a list of all the worksheet names in the current workbook. And the below will introduce 3 methods with you.

Table of Contents

## 1. Get All Worksheet Names Manually

If there are only few worksheets in your workbook, and you can get a list of all worksheet tab names by manually. Let’s see the below steps:

**#1** open your workbook

**#2** double click on the sheet’s name in the sheet tab. Press **Ctrl + C** shortcuts in your keyboard to copy the selected sheet.

**#3** create a notepad file, and then press **Ctrl +V** to paste the sheet name.

**#4** follow the above steps 2-3 to copy&paste all worksheet names into notepad file.

## 2. Get All Worksheet Names with Formula

You can also use a formula to get a list of all worksheet names with a formula. You can create a formula based on the LOOKUP function, the CHOOSE function, the INDEX function, the MID function, the FIND function and the ROWS function. Just do the following steps:

**#1** go to **FORMULAS** tab, click **Name Manager** command under **Defined Names** group. The **Name Manager** dialog will open.

**#2** click **New…** button to create a define name, type **Sheets** in the **Name** text box, and type the formula into the Refers to text box.

**=GET.WORKBOOK(1)&T(NOW())**

**#3** Type the following formula into a blank cell and press Enter key in your keyboard, and then drag the autofill handle over others cells to get the rest sheet names.

**=LOOKUP("xxxxx",CHOOSE({1,2},"",INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROWS(A$1:A1))))**

You will see that all sheet names have been listed in the cells.

## 3. Get All Sheet Names with Excel VBA Macro

You can also use an Excel VBA Macro to quickly get a list of all worksheet tab names in your workbook. Just do the following steps:

**#1** open your excel workbook and then click on “**Visual Basic**” command under **DEVELOPER** Tab, or just press “**ALT+F11**” shortcut.

**#2** then the “Visual Basic Editor” window will appear.

**#3 **click “**Insert**” ->”**Module**” to create a new module

**#4** paste the below VBA code into the code window. Then clicking “**Save**” button.

```
Sub ListWorksheetNames()
Dim ws As Worksheet
Dim cell As Range
Dim rng As Range
Dim i As Integer
' Initialize range selection prompt
On Error Resume Next
Set rng = Application.InputBox("Select a range to place the result", Type:=8)
On Error GoTo 0
' Exit if user cancels the selection
If rng Is Nothing Then Exit Sub
' Clear any existing content in the selected range
rng.ClearContents
' Loop through each worksheet and add its name to the selected range
For Each ws In ThisWorkbook.Worksheets
i = i + 1
Set cell = rng.Cells(i, 1)
cell.Value = ws.Name
Next ws
End Sub
```

**#5** back to the current worksheet, then run the above excel macro. Click **Run** button.

**#6** Let’s see the result.

## 4. Video: Get All Sheet Names

This Excel video tutorial, we’ll explore two methods to list all worksheet names. We’ll start with a formula-based approach using the LOOKUP and CHOOSE functions, followed by utilizing VBA code.

## 5. Related Functions

- Excel MID function

The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)… - Excel LOOKUP function

The Excel LOOKUP function will search a value in a vector or array.The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])… - Excel Choose Function

The Excel CHOOSE function returns a value from a list of values. The CHOOSE function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…)… - Excel INDEX function

The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])… - Excel ROWS function

The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)… - Excel FIND function

The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])

## Leave a Reply

You must be logged in to post a comment.