How to Get Excel Sheet Name (2 Methods)

This article illustrates 2 convenient ways to get the sheet name in Excel. The sheet name is a name property of the Worksheet Object in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Methods to Get Sheet Name in Excel

We can use formulas made of commonly used functions or simple VBA code to get the sheet name stored in a cell or shown in the MsgBox.

1. Use of Formula to Get the Sheet Name in Excel

As Excel doesn’t provide any built-in function to get the sheet name, we need to write a function in a combination with the MID, CELL and FIND functions. Let’s have a look at it:

Put the formula in any of the cells of the worksheet that we want to get the name. In this example, we named our worksheet name “Formula” and then put the formula in cell C5.

How the Formula Works

• The CELL function in the formula returns the complete path, workbook name, and the current sheet name. Here is the formula:

• The result we found in the previous step has enclosed the workbook name in [ ] brackets e. [excel sheet name.xlsm]. We need to find out the position of the right bracket. The current worksheet name starts immediately after the right bracket. So, the FIND function finds the position of the right bracket with the following formula and then we need to add 1 to get the position of the first string of the worksheet name.

• Finally, the MID function takes three arguments-

1st argument: =CELL(“filename”,A1) used in the first step.

2nd argument: =FIND(“]”,CELL(“filename”,A1))+1 used in the second step.

3rd argument: 31 which is the maximum length of a worksheet name in Excel

Read More: How to Search Sheet Name in Excel Workbook (2 Methods)

Alternative Formula

This formula can be used alternately which uses the RIGHT function instead of the MID function.

2. VBA Code to Retrieve and Display the Excel Sheet Name

2.1 Get Active Sheet Name in Excel by Using VBA Code

Use the simple code below to get the active sheet name.

Sub SheetName()
Range("A1") = ActiveSheet.Name
End Sub

2.2 Find Out Sheet Name by Using Index Number

By using the following code we can find out the worksheet name based on their index number. If there are multiple worksheets in a workbook, this method is helpful to find out the worksheet name easily and fast.

For Example, we are in the second worksheet named vba1. With the following code, we can find out the first sheet name which is Formula.

Sub Sheet Name()
Range("C5") = Sheets(1).Name
End Sub

With the following code, we can find out the last sheet name of a workbook. In this example, the last sheet name is VBA 2.

Sub SheetName()
Range("C6") = Sheets(Sheets.Count).Name
End Sub

Read More: How to Search Sheet Name with VBA in Excel (3 Examples)

Notes

We can also use the MsgBox function to show the VBA code result in a MsgBox if it isn’t necessary to store or use it in a cell.

Conclusion

Now, we know how to get the sheet name in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How to Apply Sheet Name Code in Footer in Excel (3 Ways)
• Select Sheet by Variable Name with VBA in Excel (2 Ways)
• How to Use Sheet Name Code in Excel (4 Applications)
• List Sheet Name in Excel (5 Methods + VBA)
• How to Rename Sheet in Excel (6 Easy and Quick Methods)
• Rename Sheet with VBA in Excel (Both Single and Multiple Sheets)
• How to Use Excel Sheet Name From Cell Value (Three Ways)