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!

 

[SunDogs]

Trebor76, I only have one workbook open, and my macros are Personal Workbook located in the XLSTART folder. But I will close it all up and start from scratch to see if I get the same results.
Thanks!

 

[SunDogs]

Trebor67, I closed everything and restarted computer, opened my workbook which automatically opens the Personal Macro Workbook. And I get the same results? I have to remove the ThisWorkbook. from that line for it to work. Are you using Excel 2016 64bit? I do believe you that it works, but it may just be a quirk with the 64 bit perhaps?

You are probably using the 32 bit version which comes standard. Even thought I bought the 64 Bit version and thought that what was loaded on my PC, a friend told me to check, and low and behold, it was 32 bit. Had to call MS to have it uninstalled and get the 64 bit version. Apparently, they want people to only load 32 bit because the 64 bit has bugs. But I'm working with very large files and need the 64 Bit.

Thanks so much and Happy Holidays!

 

[Kenneth Hobson]

You have an option to click Thanks for post or Like the post for one or more posts.

ThisWorkbook issue is not related to 32bit vs. 64bit. Sometimes you will want to use ThisWorkbook. Obviously in this case, that makes no sense in a Personal Workbook that is hidden. My example assumes ActiveWorkbook. So, it is just a matter of what you want. When we post solutions, we often have to make some guesses.

Here is a Filter() method. In it, we can make it case sensitive or not. For Each is less efficient than For i but most times, I prefer For Each to make use of the each object more easily.

The earlier solution is probably best as it will exit the loop more quickly IF a match is found.

Function wn(s$)
  Dim i As Integer, ws As Worksheet, a
  ReDim a(1 To Worksheets.Count)
  For i = 1 To Worksheets.Count
    a(i) = Worksheets(i).Name
  Next i
  a = Filter(a, s, , vbTextCompare) 'case insensitive
  'If UBound(a) >= 0 Then wn = a(0) 'worksheet name found
  If UBound(a) >= 0 Then
    wn = Worksheets(a(0)).Index 'worksheet index found
    Else: wn = ""
  End If
End Function

 

[Trebor76]

Trebor67, I closed everything and restarted computer, opened my workbook which automatically opens the Personal Macro Workbook. And I get the same results? I have to remove the ThisWorkbook.

Correct - the code as I wrote it is toggling through the tabs in the Personal workbook. I was on the right track in that you do have two workbooks open it's just that the Personal workbook is hidden (also refer Kenneth's comments). Removing the ThisWorkbook part of my code means it will run on whatever workbook you're using at the time i.e. the active workbook.

Thanks for the likes btw.

Robert

 

[SunDogs]

Kenneth Hobson and Trebor67,

I'm still learning, please forgive that I didn't mention that my macros were in the Personal WB...I learned a good lesson here! So you were right Trebor67! You guys are awesome!

Thanks again and Happy Holidays!