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

SunDogs

New Member
Joined
Oct 15, 2017
Messages
12
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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:
Code:
MsgBox ActiveSheet.Index
to use it in code:
Code:
Worksheets(2).Activate
Of course in the VBE, you can use the sheet codename rather than tabname. e.g.
Code:
Ken.Activate
View the sheet objects in VBE, Project Explorer (Ctrl+R) or from View menu.
 

SunDogs

New Member
Joined
Oct 15, 2017
Messages
12
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
Hi SunDogs,

Try this:

Code:
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

New Member
Joined
Oct 15, 2017
Messages
12
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
This is case sensitive.
Code:
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:
Code:
Option Compare Text
There are other iterative methods as well.
 
Last edited:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
Hi Trebor76, thanks but that did not work. I don't think it recognizes the "Time*".
That's odd - it worked for me :confused: 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 :confused:

so it doesn't work at this basic level nor in the code.
Incorrect - refer above.
 
Last edited:

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
If you wanted a formula or User Defined Function (UDF, formula), then you should say so.

e.g. UDF
Code:
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
 
Last edited:

SunDogs

New Member
Joined
Oct 15, 2017
Messages
12
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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
 

Forum statistics

Threads
1,082,342
Messages
5,364,783
Members
400,815
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top