Bob Phillips Wrote:
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


Private Sub Workbook_Open()
List Sheets
End Sub

Hi Bob

I get a sub not defined at this point, when I open the workbook, should
the list sheets macro be in a regular module?
nope just tried it, still says undefined sub or function


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