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