Excel VBA Get Worksheet Index: Methods and Examples

VBA & macros · Updated November 21, 2026

In Excel VBA, read a worksheet’s Index property to get its position in the workbook’s sheet-tab order: for example, ThisWorkbook.Worksheets("Data").Index. The number starts at 1 and can include chart sheets that appear before the worksheet, so use a separate count if you specifically need its position among worksheets only.

What a worksheet index means

An index is a position, not a permanent identity. If a worksheet is the first tab in a workbook, its sheet index is 1; if it is the fourth tab, its index is 4. Excel’s VBA collections are one-based, so the first item is item 1 rather than item 0. The index tells you where the sheet currently sits in the tab order, not when it was created or how important it is.

Excel exposes both a Sheets collection and a Worksheets collection. Sheets includes worksheet tabs and chart-sheet tabs, while Worksheets contains worksheets only. A worksheet’s Index property corresponds to its place in the workbook’s overall sheet order. That distinction matters whenever the workbook has chart sheets.

For example, suppose a workbook’s tabs are Summary (worksheet), Sales Chart (chart sheet), and Data (worksheet). Data has an overall sheet index of 3, but it is the second worksheet. Decide which meaning your code needs before using the number.

Get the index by worksheet name

When you know the sheet name, refer to the worksheet through its workbook and read Index. This example reports the index of the worksheet named Data in the workbook that contains the VBA code.

Sub ShowDataSheetIndex()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' ThisWorkbook is the workbook containing this code.
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")

    MsgBox ws.Index
End Sub

The code returns a number in a message box; it does not change the workbook. Replace Data with the exact worksheet tab name, including spaces and punctuation. The worksheet name is not case-sensitive in ordinary Excel references, but spelling and spaces still need to match. If the name does not exist in that workbook, assigning it raises an error, so use a check when the sheet may be absent.

Get the index of the active worksheet

If the intended target is the worksheet a person has selected, use ActiveSheet and check that it is a worksheet. The active sheet can also be a chart sheet, so treating it as a Worksheet without checking can cause a type mismatch. This code displays the index only when the active tab is a worksheet:

Sub ShowActiveWorksheetIndex()
    Dim activeObject As Object

    ' The active tab can be a worksheet or a chart sheet.
    Set activeObject = ActiveSheet

    If TypeOf activeObject Is Worksheet Then
        MsgBox activeObject.Index
    Else
        MsgBox "The active tab is not a worksheet."
    End If
End Sub

Use ActiveSheet only when the user’s current selection is genuinely part of the task. In automated procedures, relying on the active sheet can make results depend on what was clicked immediately before the macro ran. Naming the workbook and worksheet explicitly is generally easier to understand and maintain.

Count a worksheet’s position among worksheets only

If your requirement is “third worksheet” regardless of chart sheets, count only worksheet objects as you move through the workbook’s sheet order. The following example finds Data’s worksheet-only position. The counter increases for worksheets and ignores chart sheets.

Sub ShowWorksheetOnlyPosition()
    Dim wb As Workbook
    Dim target As Worksheet
    Dim sh As Object
    Dim worksheetPosition As Long

    ' Find the target in the workbook that contains this code.
    Set wb = ThisWorkbook
    Set target = wb.Worksheets("Data")

    For Each sh In wb.Sheets
        If TypeOf sh Is Worksheet Then
            worksheetPosition = worksheetPosition + 1
            If sh Is target Then Exit For
        End If
    Next sh

    MsgBox worksheetPosition
End Sub

For the earlier example, this code reports 2 for Data, while target.Index reports 3. Use the loop when a later step expects an ordinal within the Worksheets collection. If the only goal is to access a particular worksheet, avoid converting it to a number at all: a direct reference such as wb.Worksheets("Data") is usually clearer and less vulnerable to tab rearrangement.

Choose the right workbook reference

ThisWorkbook means the workbook where the running VBA code is stored. ActiveWorkbook means the workbook currently active in Excel. They may be different: for example, code stored in an add-in or the Personal Macro Workbook can act on a separate file that the user opened. Using the wrong reference can return an index from the wrong workbook or fail because that workbook does not contain the requested name.

If the macro should operate on the file containing its code, keep ThisWorkbook. If it should operate on the workbook the user is currently editing, use ActiveWorkbook and account for the possibility that no suitable workbook is active. For repeatable automation, a variable assigned to a known workbook is even more explicit:

Sub ShowIndexInActiveWorkbook()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Use the workbook currently active in Excel.
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Data")

    MsgBox ws.Index
End Sub

Qualifying the worksheet with its workbook, as in wb.Worksheets("Data"), avoids ambiguity. An unqualified reference such as Worksheets("Data") generally refers to the active workbook, which can change as a macro opens files or switches windows.

What happens when sheets move or names change

An index is dynamic. If a user drags a tab to a new location, inserts another sheet before it, or deletes a preceding sheet, the worksheet’s index can change. A macro that stores the number 3 and assumes it will always mean Data is therefore fragile. Re-read the property when needed, or identify the target by name or by its VBA CodeName.

A sheet’s visible Name is the tab label and can be changed by a user. The VBA CodeName is a separate identifier shown in the Visual Basic Editor’s Properties window; it can be useful for code that is tied to a specific sheet in the workbook, because changing the tab label does not change the CodeName. Neither approach turns the sheet’s position into a fixed value: inserting tabs can still alter its index.

Use an index when order itself matters, such as processing worksheets from left to right. Use a name when the task is about a particular named tab. Use a CodeName when VBA should continue referring to a particular sheet even if someone renames its visible tab. Choosing based on the job prevents later sheet reordering from silently changing what a macro targets.

Common errors and safer handling

The most common failure is requesting a sheet name that is not present in the workbook reference. A misspelling, an extra space, or looking in ThisWorkbook when the sheet is in ActiveWorkbook can trigger a “Subscript out of range” error. If a sheet is optional, test for it before reading its index rather than letting the macro stop unexpectedly.

Sub CheckForDataSheet()
    Dim ws As Worksheet

    ' Limit error suppression to the sheet lookup.
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Data")
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "The Data worksheet was not found."
    Else
        MsgBox ws.Index
    End If
End Sub

Keep On Error Resume Next confined to the lookup, then restore normal error handling with On Error GoTo 0. Leaving error suppression active for the rest of a large procedure can conceal unrelated mistakes. Also remember that a chart sheet is not a member of Worksheets; looking for a chart tab by using Worksheets("Sales Chart") will not find it. Use Sheets when the target may be any kind of sheet.

Run the VBA safely in Excel

In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste a macro into the standard module. On Mac, use Option+F11 (some keyboards require Fn as well), or open the editor from the Developer tab. Excel for the web does not run VBA macros; use the desktop app to execute this code.

Save a copy of the workbook before running a macro. These examples only display information, but other macros can change or delete workbook content, and VBA actions cannot be undone with Ctrl+Z. To preserve VBA in a workbook, save it in a macro-enabled format such as .xlsm; saving as a macro-free workbook format can remove the VBA project. Run macros only when you understand what they do and trust their source.

Frequently Asked Questions

Does Worksheet.Index start at zero or one?
It starts at 1. The first sheet in a workbook’s sheet order has index 1, consistent with Excel’s one-based collections.

Does a chart sheet count in a worksheet’s index?
Yes. The worksheet’s overall tab position counts chart sheets in the workbook’s sheet order. To get a worksheet-only position, loop through Sheets and increment a counter only for objects that are worksheets.

How do I get the worksheet name instead of its index?
Use the worksheet’s Name property, for example ws.Name. The name identifies the visible tab label; ws.Index gives its current position.

Can a worksheet’s index change while the workbook is open?
Yes. Moving, inserting, or deleting sheet tabs can change the index. Read it when the macro needs it rather than treating a previously saved index as a permanent identifier.

Can I run this VBA in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but it cannot run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the Visual Basic Editor and execute VBA.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.