Find chars in SheetName (Excel 2003)

Topic

New Reply

Hi,

I need to display all the sheets that contain the characters (M) in the name, and place them on a sheet. These characaters can be anywhere in the name and the name can be any length.
(this macro also places hyperlinks on the names). I just about have it, but I do not have the correct syntax … below is what I have.
Again, your help is appreciated!
–cat


Sub PrintSheetNames()
N = ActiveWorkbook.Sheets.Count
For i = 1 To N
If ActiveWorkbook.Sheets(i).Visible Then
'Males first
If Find("(M)", ActiveWorkbook.Sheets(i).Name) = True Then
Sheets("Input-General").Range("A" & 129 + i) = ActiveWorkbook.Sheets(i).Name
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & 129 + i), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A129", _
TextToDisplay:=Sheets(i).Name
End If
'Females next
If Find("(F)", ActiveWorkbook.Sheets(i).Name) = True Then
Sheets("Input-General").Range("D" & 129 + i) = ActiveWorkbook.Sheets(i).Name
ActiveSheet.Hyperlinks.Add Anchor:=Range("D" & 129 + i), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!D129", _
TextToDisplay:=Sheets(i).Name
End If
End If
Next i
Range("A" & 129 & ":A" & 129 + N).Sort Key1:=Range("A129")
Range("D" & 129 & ":D" & 129 + N).Sort Key1:=Range("D129")
End Sub


Reply | Quote

Replies

Instead of

If Find("(M)", ActiveWorkbook.Sheets(i).Name) = True Then

use

If ActiveWorkbook.Sheets(i).Name Like "*(M)*" Then

or

If InStr(ActiveWorkbook.Sheets(i).Name, "(M)") > 0 Then

The Like operator lets you compare a string with wildcards – * stands for any number of characters. The InStr function is the VBA function that searches for text in a string.

Reply | Quote

Hans, you are wonderful !!! You are always there when I need you. This forum is really making me look good at work !
Thanks,
–cat

Reply | Quote