Written by Allen Wyatt (last updated April 6, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
Lawrence needs a way to determine the number of a worksheet even if the worksheet has been renamed. For instance, if a worksheet is named Sheet11 it is easy enough to figure out that it is sheet 11. If he renames the sheet to January, Lawrence still needs a way to know this is sheet 11.
The solution to this problem is best done with a user-defined function (a macro). There are, in reality, two numbers that the macro could return for each worksheet. The first is the index number for the worksheet. This number represents the index of the worksheet's Worksheet object within the Worksheets collection. This value can be returned by a macro similar to the following:
Function SheetNumber1(shtname As String) Dim sht As Worksheet Application.Volatile For Each sht In ThisWorkbook.Worksheets If LCase(sht.Name) = LCase(shtname) Then SheetNumber1 = sht.Index Exit Function End If Next SheetNumber1 = -1 End Function
This function, when used in a worksheet, will return the index number of any worksheet whose name is passed to the function. If the name that is passed to the function doesn't exist in the worksheets collection, then a value of -1 is returned by the function. For instance, the following used in a cell would return the index value for the worksheet named "January" within the collection:
=SheetNumber("January")
The problem with this approach is that the order of Worksheet objects in the Worksheets collection can change over time. Thus, you can't always assume that the eleventh sheet in the collection is the sheet that was originally Sheet11.
A more consistent way of figuring out the original name for a worksheet (regardless of how it is renamed) is to use what Visual Basic refers to as the sheet's "CodeName." This is a property of the worksheet and can be determined in the following manner:
Function SheetNumber2(shtname As String) Dim sht As Worksheet Dim sTemp As String Application.Volatile For Each sht In ThisWorkbook.Worksheets If LCase(sht.Name) = LCase(shtname) Then sTemp = sht.CodeName SheetNumber2 = Val(Mid(sTemp, 6, 4)) Exit Function End If Next SheetNumber2 = -1 End Function
The CodeName property is read-only in a macro. It is assigned at the time that the worksheet is created, but it is possible for it to be manually changed within the Visual Basic editor. The CodeName is always a string, representing the very first name that was applied to the worksheet, so it will be something like "Sheet11". Once the CodeName is set, even if the worksheet is renamed (such as to "January"), it will remain stable ("Sheet11").
In the macro example (SheetNumber2) the CodeName property is assigned to the sTemp variable. This will, most of the time, be something like "Sheet3" or "Sheet11". So, the macro then grabs the numeric value of whatever begins with the sixth character (right after "Sheet"). This is the value that is returned by the function.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3398) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Determining a Worksheet's Number.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Got a workbook with a lot of worksheets in it? Here's some handy ways to jump to the worksheet you want, alphabetically.
Discover MoreCopy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it ...
Discover MoreIf you want someone to not be able to move from one worksheet to another in a workbook, you've got your work cut out for ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments