Count and List only WorkSheets named (anything)&"-pilot"
 

I need help with the following code to count and list only worksheets
that contain the name "(anything)&-Pilot". I have code that installs
the worksheet and names it, but I can't get a list of sheets with
"(anything)&-Pilot".
TIA for any help,
Doug

Present code:

Private Sub CountPilots()
Dim Plt As Long
'count only worksheets named (anything)-Pilot
With ActiveWorkbook.Worksheets(Array("" & "," & "pilot", "" &
"-pilot", "" & "- pilot"))
Worksheets(Array("" & "," & "pilot", "" & "-pilot", "" & "-
pilot")).Count = Plt
End With
'Place name of all worksheets named (anything)-pilot on activesheet
or "Name-List" sheet
For Plt = 1 To Worksheets.Count
ActiveCell(Plt, 1).Value = Worksheets(Plt).Name
Next Plt
End Sub

Count and List only WorkSheets named (anything)&"-pilot"
 

Dim rng as Range
Dim icnt as Long
set rng = ActiveCell
for each sh in ThisWorkbook.Worksheets
if instr(1,sh.name, "-pilot",1) then
rng.offset(icnt,0).Value = sh.name
icnt = icnt + 1
end if
Next


If you will accept just "anything" & "pilot" without the hyphen, then change
to

Dim rng as Range
Dim icnt as Long
set rng = ActiveCell
for each sh in ThisWorkbook.Worksheets
if instr(1,sh.name, "pilot",1) then
rng.offset(icnt,0).Value = sh.name
icnt = icnt + 1
end if
Next
--
Regards,
Tom Ogilvy


"Doug" wrote in message
om...
I need help with the following code to count and list only worksheets
that contain the name "(anything)&-Pilot". I have code that installs
the worksheet and names it, but I can't get a list of sheets with
"(anything)&-Pilot".
TIA for any help,
Doug

Present code:

Private Sub CountPilots()
Dim Plt As Long
'count only worksheets named (anything)-Pilot
With ActiveWorkbook.Worksheets(Array("" & "," & "pilot", "" &
"-pilot", "" & "- pilot"))
Worksheets(Array("" & "," & "pilot", "" & "-pilot", "" & "-
pilot")).Count = Plt
End With
'Place name of all worksheets named (anything)-pilot on activesheet
or "Name-List" sheet
For Plt = 1 To Worksheets.Count
ActiveCell(Plt, 1).Value = Worksheets(Plt).Name
Next Plt
End Sub