Well i dont know how to do this but i thought i would do the leg wor
for you....this has been ripped off from Chip Pearsons site....h
writes:-

It is very simple to retrieve sheet names in VBA. They are stored i
two collection objects in the ActiveWorkbook object: the Sheet
collection and the Worksheets collection. The Sheets collectio
contains both worksheets and chart sheets. The Worksheets collectio
contains only worksheets.

To retrieve the name of the first sheet in the workbook, use

Public Function FirstSheetName()
FirstSheetName = Sheets(1).Name
End Function

To retrieve the name of the last sheet in the workbook, use

Public Function LastSheetName()
LastSheetName = Sheets(Sheets.Count).Name
End Function

You can return an array of all the sheet names with the following

Public Function AllSheetNames()
Dim Arr() As String
Dim I as Integer
Redim Arr(Sheets.Count-1)
For I = 0 To Sheets.Count - 1
Arr(i) = Sheets(I+1).Name
Next I
AllSheetNames = Arr ' return a row array OR
AllSheetNames = Application.Worksheetfunction.Transpose(Arr)
' return a column array
End Function


HTH

Simo

--
Message posted from http://www.ExcelForum.com