Previous Thread

7/11/2006 1:51:14 PM    Filtered List of Sheet Names
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



7/11/2006 2:57:42 PM    Re: Filtered List of Sheet Names
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 
 
Kevin wrote: 
 
-- 
 
Dave Peterson