VBA Wildcard Characters -- Selecting a Tab

[jh0]

Good afternoon,

Hope this one isn't too basic but it has been giving me headaches. I am basically just trying to select a tab that is always going to be called "Order Detail xyz" I think I need to use a like to select it -- here is all i have so far and I can't get it to work

Sheets("Order Detail *").Select

Thanks in advance!

 

[jeffreybrown]

If the tab is always called Order Detail xyz, then why not...

Sheets("Order Detail xyz").Select

BTW: You rarely ever have to use .Select to work with an object

 

[jh0]

If the tab is always called Order Detail xyz, then why not...

Sheets("Order Detail xyz").Select

BTW: You rarely ever have to use .Select to work with an object

Sorry, I guess I wasn't clear. I was using "xyz" to denote the portion of the name that varies. It is typically a date in numeric format that is different from one instance to the next.

The select portion does make some sense to me. All i'm trying to do here is rename this tab as part of the macro, and I suppose I don't need the two lines instead of one, but I just can't reference it.

Hope I was clearer.

Thanks,

 

[phagan]

"Order Detail *" will always look for exactly "Order Detail *". You want something more like ("Order Detail " & *). But you might find it easier to select it by tab number

Sheet5.select

 

[jh0]

"Order Detail *" will always look for exactly "Order Detail *". You want something more like ("Order Detail " & *). But you might find it easier to select it by tab number

Sheet5.select

Unfortunately the tabs are not always in the same location, so using something like selecting the number of the sheet won't work all of the time. I tried in your recommendation:

Sheets("Order Detail " & [I][SIZE=3][B]*[/B][/SIZE][/I]).Select

But I get an "Compile Error: Expected Expression" focused around the *. Any idea why?

 

[Rick Rothstein]

It is typically a date in numeric format

Can you expand on this a little... where is the date at (cell or variable), what do you mean by "in numeric format", and what exactly does it have to look like when combined with the "Order Detail" text?

 

[jh0]

Can you expand on this a little... where is the date at (cell or variable), what do you mean by "in numeric format", and what exactly does it have to look like when combined with the "Order Detail" text?

The date is in the tab name part of the name. "Order Detail 0612" is a literal example of what I'm looking at. I basically just need to select that tab here and rename it to just say "Order Detail" (I know it's stupid but we have an extremely picky system that this file has to get uploaded to)

What I would love to do is just:

Sheets("Order Detail " & *).Name = "Order Detail"

But I am now getting an error after the last tip, or otherwise getting an error when the macro is run.

Thanks Rick

 

[JoeMo]

Assuming there is only one tab that contains "Order Detail":

Sub test()
For Each sh In ActiveWorkbook.Sheets
If sh.Name Like "Order Detail*" Then
 sh.Name = "Order Detail"
exit sub
end if
Next sh
End Sub

 

[jh0]

Assuming there is only one tab that contains "Order Detail":

Sub test()
For Each sh In ActiveWorkbook.Sheets
If sh.Name Like "Order Detail*" Then
 sh.Name = "Order Detail"
exit sub
end if
Next sh
End Sub

Thanks Joe,
How could i go about writing it so that it doesn't loop and I could have it in a macro that does a lot more, as opposed to having a macro dedicated to only this. (When I "step through" this, it loops and i'm not sure how to end it)

 

[JoeMo]

If you want to find a sheet among many sheets in a workbook I don't see a way to avoid looking at them sheet by sheet until you find the one you want. The code I wrote stops looping once the sheet is found. This can easily be adapted to whatever code you are using. For example you might need to replace the Exit Sub line with "Exit For" or "Exit Do", etc.