Filtered List of Sheet Names

[Kevin]

I have a large workbook with two types of sheets. One type has detailed
info. and is always named after a five-figit number. The summary pages
are titled things like "summary" or "overview". I have around 100
detail pages and about 15 summary pages.

1. I need to build an array of sheet names that meet the criteria of
having a five-digit number for their name.
2. I need to print that array in a list on one of the summary pages
starting in cell b15 in ascending order.

I've never done Macro programming (I've tried for so long to avoid it
due to slowness, but now I have to take the plunge). I can imagine
matching the five-digit number with regular expressions in other
programming languages. Can it be done in Excel, and if not, is there a
backward way to achieve the goal?

Thank you for any help you can provide.

-Kevin

 

[Dave Peterson]

Maybe something like this:

Option Explicit
Sub testme()

Dim SumWks As Worksheet
Dim DestCell As Range
Dim wks As Worksheet
Dim iCtr As Long

Set SumWks = Worksheets("Summary") 'whatever you need

Set DestCell = SumWks.Range("B15")

iCtr = -1
For Each wks In ActiveWorkbook.Worksheets
If IsNumeric(wks.Name) Then
If Len(wks.Name) = 5 Then
iCtr = iCtr + 1
DestCell.Offset(iCtr, 0).Value = "'" & wks.Name
End If
End If
Next wks

With DestCell.Resize(iCtr + 1, 1)
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm