VBA: Select Excel's Worksheet using wildcard

[maxifire]

Hi guys,

Sorry for the long post.

I'm having problem using wildcard to select the appropriate worksheet
in Excel.

This is what I have in the workbook.

In 1 workbook, I have 3 different worksheets and they are namely:

1. Week ABC
2. Week DEF
3. Week GHI

Say if I want to select the worksheet: 'Week ABC', I use either one of
the following codes to open the desired worksheet:

1. Sheets("Week A??").Select
2. Sheets("Week AB?").Select
3. Sheets("Week A*").Select
4. ..... and etc.

When I execute one of the aboved codes in VBA, I keep getting the
error: "Runtime error 9: The subscript is out of range."

Could someone please clarify is wildcard supported in the aboved
statements? If no, is there any way I can work around in order to make
it work? I need to automate the process of sheets selection as there
are many values in the sheet to calculate.

Any clue anyone?

Thanks.


Rgds

 

[Dave Peterson]

I think you'll have to look for a match another way:

Option Explicit
Sub testme01()

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If LCase(wks.Name) Like "week a??" Then
wks.Select
Exit For
End If
Next wks

End Sub

might work for you.

 

[maxifire]

Thanks Dave! I'll try it out on Monday when I'm back in office. :

 

[maxifire]

Hi Dave,

Sad to say, the code doesn't seem to work in my case.
4 more days left to deadline, any other idea?

Thanks.

Rgds

 

[maxifire]

Hey! The code works after I've did some minor modifications to it.
But now, I've ran into another problem.

I have 3 worksheets and they are: Week 1 (P08), Week 2 (P08), and Wee
3 (P08).

The code did select the first worksheet: Week 1 (P08) and I thought
could run a For loop so that it will select the next worksheet whic
is: Week 2 (P08). But after many attempts, it failed to select the nex
worksheet.

Is there any way I can make the code select the next worksheet as
need to retrieve values out from these 3 worksheets.

Thanks for the help !


Rgd

 

[Steve Hieb]

Confused, but maybe something like this is what you're looking for:

ActiveSheet.Next.Select

This will select the next sheet to the right of whichever one is
active when this executes. If the farthest right sheet (last sheet)
is active when this runs, it will return an error (#91) that you'll
need to handle.

Regards,
Steve Hieb