Short Description
Create a simple Excel function that returns the name of the Worksheet where that cell is.
Ingredients
- Microsoft Excel
The Need
When working with Excel Workbooks that contain multiple Worksheets, you might need to get the name of the each Worksheet in formulas. For example, I have a few reporting templates where the main title of each page corresponds to the Worksheet name. In that scenario it is handy to have the name of the Worksheet automatically change in the title of the report.
This technique is particularly handy to create preformatted Worksheets which you can then copy/paste and customise, starting with their name.
The Recipe
The easiest way to achieve this functionality is by creating a custom function or formula. This formula is just a few lines of code as shown below.
Public Function GetSheetName() As Variant ' -------------------------------------------------------------- ' Comments: ' Gets the name of the sheet that the current cell belongs to ' ' Date Developer Comment ' -------------------------------------------------------------- ' 20/02/16 Engineers Tools Initial version Application.Volatile Dim rngCurrentCell As Excel.Range Set rngCurrentCell = Application.ThisCell GetSheetName = rngCurrentCell.Worksheet.Name Set rngCurrentCell = Nothing End Function
The steps executed by the function are:
1) The Application.Volatile indicates that this function is run every time that the workbook is recalculated
2) The Application.ThisCell returns a reference to the cell calling the function
3) The Worksheet.Name property of the cell returns the name of the worksheet tab
Use the function like any other Excel formula in any of your worksheets. If you change the name of the worksheet, the cells using this formula will update accordingly.
That’s it!. We hope you found this recipe useful. If you did, please share this post in the social network of your choosing by clicking one of the buttons below.