How to search a string in a sheetname?

I am trying to search a particular string in all s开发者_运维百科heet names of a workbook using For each loop as follows:

 For Each Sheet In ActiveWorkbook.Sheets
  Name = ActiveSheet.Name
  Next Sheet

But i am not able to get all the sheet names using above code as it gets stuck up only one sheet name.

After getting the sheet name i want to search a particular string in the sheet name So it would be great if any one can help me in searching a string in all sheets.

For Each sheet In ActiveWorkbook.Sheets
    If sheet.Name Like "*" & strSearch & "*" Then
        Debug.Print "Found! " & sheet.Name
    End If
Next

You iterate over all sheets, but you always use ActiveSheet.Name in your loop. To search for a pattern, you can use Like with the * wildcard.

You can use the InStr function as well... or maybe I didn't get your question correctly.

Sub CheckSheets()        
    Dim oSheet As Excel.Worksheet
            For Each oSheet In ActiveWorkbook.Sheets           
        If InStr(UCase(oSheet.Name), UCase("Sheet")) Then                
            Debug.Print oSheet.Name                
        End If            
    Next oSheet        
End Sub