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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20		 Public``Function``GetSheetName()``As``Variant
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.