Obtain displayed order of Excel worksheets

Question

I'd like to find the position of a worksheet as it is displayed in a workbook.

For example, assume I have a workbook starting with Sheet1, Sheet2 and Sheet3 in that order. Then a user drags Sheet2 to left, before Sheet1.

I want Sheet2 to return 1, Sheet1 to return 2 (and Sheet3 still to return 3).

I can't find a way to determine this in VBA.

Answer 1

This should do it:

Worksheets("Sheet1").Index

https://msdn.microsoft.com/en-us/library/office/ff836415.aspx

Answer 2

You can just iterate the Worksheets collection of the Workbook object. You can test yourself by running the following code, switch the order around in the UI, then run it again:

Option Explicit

Sub IterateSheetsByOrder()

    Dim intCounter As Integer
    Dim wb As Workbook

    Set wb = ThisWorkbook

    For intCounter = 1 To wb.Worksheets.Count
        Debug.Print wb.Worksheets(intCounter).Name
    Next intCounter

End Sub

Answer 3

To loop through all worksheets in a workbook use For Each WS in ThisWorkbook.Worksheets where WS is a worksheet object. Hence to obtain order of Excel worksheets as shown, we may also use the following code:

Sub LoopThroughWorksheets()
Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets
        Debug.Print WS.Name
    Next

End Sub

To obtain an output like Worksheets("Sheet1").Index then you may use this code

Sub IndexWorksheet()
Dim WS As Worksheet, n As Long

    For Each WS In ThisWorkbook.Worksheets
        n = n + 1
        If WS.Name = "Sheet1" Then Debug.Print n
    Next

End Sub

Answer 4

You can use the Sheets object. In your example, reading Sheets(2).Name should return Sheet1.

Answer 5

Right answer provided by Anastasiya-Romanova, but missing some important details.

There are two methods of doing this. First, with a For Each loop:

Sub ListSheetNames()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

Second, with a basic For loop:

Sub ListSheetNames
    Dim i As Long

    For i = 1 to ThisWorkbook.Worksheets.Count
        Debug.Print ThisWorkbook.Worksheets(i).Name
    Next i
End Sub

You will find the second method will always output the names in the sheet index order, which is generally the order the sheets were created in unless you change the index. Simply rearranging the sheets from the workbook window won't change the index.

Therefore, the first method is the correct way to do this. It will always follow the tab order as you see on your screen.

Answer 6

Below code works even if sheet is renamed or its sequence is changed.

Sub Display_Sheet_Tab_Number()

        Dim WorksheetName As String
        Dim n As Integer

        WorksheetName = Sheet1.Name
        MsgBox Worksheetname
        n = Sheets(WorksheetName).Index     'n is index number of the sheet
        MsgBox "Index No. = " & n

End Sub