Excel.Tips.Net ExcelTips (Menu Interface)

Determining a Worksheet's Number

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Determining a Worksheet's Number.

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

    For Each sht In ThisWorkbook.Worksheets
         If LCase(sht.Name) = LCase(shtname) Then
              SheetNumber1 = sht.Index
              Exit Function
         End If
    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:


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

    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
    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.

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.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

WildcatMidnight    19 Oct 2016, 02:13
But how when I move (drag) one Sheet →
To another position ← Pls resolve, thanks!
evan    15 Feb 2012, 17:24
Is there a way to automatically rename every worksheet to the sheet's index number? Ie, the one on the far left is automatically renamed '1', the second sheet is automatically renamed '2', etc?

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.