search sheet names containing phrase then copy to cell

[Nick]

I am trying to make a macro that will copy and paste the names of all sheets
in my workbook that contain the phrase “MR3.†I would like the macro to
search for all the sheets that have “MR3†in their name and copy those sheet
names in row 3 across columns starting in column D of a different sheet in
the same workbook. For example if I have sheets named “MR3_Run1â€,
“MR3_Run2_BC†and “Comparison 2009â€; the macro will fill cell D3 with
“MR3_Run1†then cell E3 with “MR3_Run2_BC†in my sheet called “comparison
2009â€. Here is what I have so far:

Option Explicit
Dim counter As integer
Dim wks As Worksheet

Sub Add_MR3()
For Each wks In Worksheets
if instring(1,wks.Name,"MR3",text)>0
sheets(2008 Comparison) cells (3,counter)=wks.Name

end if

End Sub


Thanks,

 

[JLGWhiz]

I did not test it, only cleaned it up a little.

Option Explicit

Sub Add_MR3()
Dim counter As integer
Dim wks As Worksheet
Counter = 1
For Each wks In ThisWorkbook.Worksheets
If InStr(1,wks.Name,"MR3",vbTextCompare)>0 Then
Sheets("2008 Comparison").Cells(3,counter)=wks.Name
Counter = Counter + 1
End If
Next
End Sub