Hello Simon,

Thanks for the post, but I'm not after sheet names. The problem concerns defined Names. Please see my other replies.

Brian


"Simon Lloyd " wrote in message ...
Well i dont know how to do this but i thought i would do the leg work
for you....this has been ripped off from Chip Pearsons site....he
writes:-

It is very simple to retrieve sheet names in VBA. They are stored in
two collection objects in the ActiveWorkbook object: the Sheets
collection and the Worksheets collection. The Sheets collection
contains both worksheets and chart sheets. The Worksheets collection
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

Simon


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