Here is some code

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)
ListSheets
End Sub

Private Sub Workbook_Open()
List Sheets
End Sub

Private Sub ListSheets()
Dim wsh As Worksheet
Dim Sh As Object
Dim i As Long

Application.ScreenUpdating = True
Application.EnableEvents = False

On Error Resume Next
Set wsh = Worksheets("ListAll")
On Error GoTo 0

On Error GoTo ListSheets_exit

If Not wsh Is Nothing Then
wsh.Cells.ClearContents
Else
Set wsh = Worksheets.Add
wsh.Name = "ListAll"
End If

For Each Sh In ThisWorkbook.Sheets
If Sh.Name < wsh.Name Then
i = i + 1
wsh.Cells(i, "A").Value = Sh.Name
End If
Next Sh

wsh.Activate

Set wsh = Nothing
Set Sh = Nothing

ListSheets_exit:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"sparx" wrote in
message ...

Can anybody help by providing a formula or macro that will list all the
worksheets in a workbook regardless if I add some new worksheets at a
later stage - I want to be able to see in one sheet - all the available
worksheets that make up my workbook.


--
sparx
------------------------------------------------------------------------
sparx's Profile:
http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=517213