MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Tab Names and Wildcards


Posted by Sherry on February 15, 2002 4:38 PM

I need some help with a Macro. I have several (over 25) different spreadsheets that I am opening via a macro, looping through a folder, opening one by one.

That's the easy part, the hard part is that in each spreadsheet, there are multiple tabs. The tab that I need to pull in each sheet ends with the word UPL (for upload). For example, there's a tab called "Region 5 UPL" in the first spreadsheet, the second spreadsheet has a tab called "Region 6 UPL" and so on.

I need to tell the macro to select sheets containing the letters "UPL". Anyone know how to do that?? I tried Sheets.("*UPL*).Select but that does not work (I was hoping the * would represent a wildcard. Please help if you can. Thanks!!!!!!


Posted by Pandarus on February 15, 2002 5:09 PM


Dim ws As Worksheet
For Each ws In Worksheets
If Right(ws.Name, 3) = "UPL" Then
MsgBox ws.Name
End If
Next


Posted by Dave on April 22, 2002 3:33 AM


I'm trying to write a code which opens all files in a particular directory (actual number varies but, the file extensions are the same). You mentioned above that you can do this ("I have several (over 25) different spreadsheets that I am opening via a macro, looping through a folder, opening one by one")? if what you ment is that you have a line of code which opens all the files in a particular directory could you post the code please as i'm getting nowhere!

i've tried the whole:

workbooks.opentext filename:= "C:\Test\" & "*" & ".txt" [etc.]

idea but, to no avail.

Thanks,
Dave