Using * to find a worksheet name so I can know what the worksheet number is

[SunDogs]

Hello,

I have a worksheet with a long name "Timeline_123456789.24", and I want to know what sheet number it is. I don't want to have to type the whole name, since it's the only worksheet with this name.

I was hoping to use "Temp*" to find it but it doesn't work.

Is there a way to use a wildcard to find this worksheet?

Thanks!

 

[Kenneth Hobson]

Your question is unclear. Do you mean find manually, in code, or in a formula?

Manually, you can right click the bottom left and then start typing the sheetname to drill down to it.

In code, you can use the Index number. Of course that changes. If you make the sheet active, you can find it by:

MsgBox ActiveSheet.Index

to use it in code:

Worksheets(2).Activate

Of course in the VBE, you can use the sheet codename rather than tabname. e.g.

Ken.Activate

View the sheet objects in VBE, Project Explorer (Ctrl+R) or from View menu.

 

[SunDogs]

Hi Kenneth, thanks for the quick reply.
Sorry that I omitted that I am trying to create a macro for this.
The WS tab name is long and I wanted to find it without having to know everything after the Time.... name.
Can I search for this WS name with a wildcard?
Thanks

 

[Trebor76]

Hi SunDogs,

Try this:

Option Explicit
Sub Macro1()

    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Worksheets
        If wsMySheet.Name Like "Time*" Then
            MsgBox "Index number of desired sheet is " & wsMySheet.Index
            Exit For
        End If
    Next wsMySheet
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[SunDogs]

Hi Trebor76, thanks but that did not work. I don't think it recognizes the "Time*".
If I select that sheet and in an empty cell I type =Sheet("Time*"), I get a #N/A, so it doesn't work at this basic level nor in the code.
Thanks so much for trying!

 

[Kenneth Hobson]

This is case sensitive.

Sub Test()
  Dim w As Worksheet, ws As Worksheet
  For Each ws In Worksheets
    If ws.Name Like "Ken*" Then Set w = ws
  Next ws
  If Not w Is Nothing Then MsgBox w.Name
End Sub

You can make it case insensitive by adding this to top of Module:

Option Compare Text

There are other iterative methods as well.

 

[Trebor76]

Hi Trebor76, thanks but that did not work. I don't think it recognizes the "Time*".

That's odd - it worked for me The code loops through each sheet in the active workbook and if a tab starts with "Time" it returns the index number of that tab in a message box. Isn't that what you needed?

If I select that sheet and in an empty cell I type =Sheet("Time*"), I get a #N/A

Correct. I wrote a piece of code not a User Defined Function (UDF). My code has nothing to do with your proposed formula

so it doesn't work at this basic level nor in the code.

Incorrect - refer above.

 

[Kenneth Hobson]

If you wanted a formula or User Defined Function (UDF, formula), then you should say so.

e.g. UDF

Option Compare Text

'=wn("ken")
Function wn(s$)
  Dim w As Worksheet, ws As Worksheet
  For Each ws In Worksheets
    If ws.Name Like s & "*" Then
      Set w = ws
      Exit For
    End If
  Next ws
  If Not w Is Nothing Then
    wn = w.Name 'wn = w.Index
    Else: wn = ""
  End If
End Function

 

[SunDogs]

Kenneth Hobson, yours worked! Thanks so much!


Trebor 67, yours works if I change your code line:
For Each wsMySheet in ThisWorkbook.Worksheets
to
For Each wsMySheet in Worksheets


which is how Kenneth Hobson did it. I'm using Excel 2016 64 bit.
So you both are right, maybe there is just something quirky with the Excel 2016 64 bit?
This is only the second time I use this site and I've had exceptional response and closure to my problems!
How do I give both of you guys credit for solving this?
Thanks!

 

[Trebor76]

Trebor 67, yours works if I change your code line:
For Each wsMySheet in ThisWorkbook.Worksheets
to
For Each wsMySheet in Worksheets

That suggests to me that you have more than one workbook open and the workbook you've put the code on isn't the one that has a tab that starts with "Time".

Glad you got a working solution.

Robert