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.

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. Using Excel Formula to Get the Sheet Name

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

Put the formula in any of the cells of the worksheet. In this example, we named our worksheet Formula and then put the formula in cell C5.

How Generic 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.g. [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.

Alternative Formula

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

2. Applying VBA Code to Retrieve and Display Sheet Name

2.1 Getting Active Sheet Name with Excel VBA Code

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

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

2.2 Finding 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

Things to Remember

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

Download Practice Workbook

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

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 Insert Excel Sheet Name from Cell Value
• How to List Sheet Name in Excel
• How to Rename Sheet in Excel
• How to Use Sheet Name Code in Excel
• How to Apply Sheet Name Code in Footer in Excel
• How to Search by Sheet Name in Excel Workbook

<< Go Back to Excel Sheet Name | Excel Worksheets | Learn Excel