VBA: Select Excel's Worksheet using wildcard
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 --- Message posted from http://www.ExcelForum.com/ |
VBA: Select Excel's Worksheet using wildcard
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 wrote: 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 --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
VBA: Select Excel's Worksheet using wildcard
Thanks Dave! I'll try it out on Monday when I'm back in office. :
-- Message posted from http://www.ExcelForum.com |
VBA: Select Excel's Worksheet using wildcard
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 --- Message posted from http://www.ExcelForum.com/ |
VBA: Select Excel's Worksheet using wildcard
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 a 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 -- Message posted from http://www.ExcelForum.com |
VBA: Select Excel's Worksheet using wildcard
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 |
All times are GMT +1. The time now is 02:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com