CODEWITHSUNDEEP
excelvba
knox
knox

Reputation: 33

Find Sheet Name and get Sheet Index

I'm trying to find a sheet by name and get the index of this sheet. I tried it this way but it isn't working.

For Each Sheet In ThisWorkbook.Sheets
    If Sheet.Name Like "*Consolidated EOY*" Then
        Sheet_Nr = Sheet.Index
    End If
Next

What am I missing?

Upvotes: 1

Views: 1174

Answers (2)

knox
knox

Reputation: 33

If I change the "ThisWorkbook" to the name of the workbook where the sheets are then it works. 

For Each Sheet In WB_New.Sheets
    If Sheet.Name Like "*Consolidated EOY*" Then
        Sheet_Nr = Sheet.Index
    End If
Next

Upvotes: 2

Variatus
Variatus

Reputation: 14383

The use of Like is obviously too imprecise. Here is a function that doesn't need it.

Function SheetIndex(NamePartial As String) As Integer

    Dim Sheet       As Worksheet

    For Each Sheet In ThisWorkbook.Worksheets
        With Sheet
            If InStr(.Name, NamePartial) Then
                SheetIndex = .Index
            End If
        End With
    Next
End Function

Call the function with syntax as shown below. If you know that the name you want starts with the partial name you have I recommend to modify the above code using this line instead of the existing, If InStr(.Name, NamePartial) = 1 Then

Private Sub TestIndex()
    Debug.Print SheetIndex("Consolidated EOY")
End Sub

I point out that the Sheets collection isn't identical to the Worksheets collection and that the data type you are looking for is a Worksheet. As a matter of principle, if you don't know the difference between a Sheet and a Worksheet I would recommend that you stick to the kind you do know. From the apect of logic, why should you waste time searching the bigger Sheets collection while all you want is contained in the collection of ThisWorkbook.Worksheets.

Upvotes: 0

Related Questions