I have not woken up yet. You might delete sheets so you will need to clear
the original list. The rest is OK - it still goes in the workbook module.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Sheets("Menu").Select
Range("B2").Select
' you might delete sheets so the updated
' list will be smaller
Range("B2").CurrentRegion.ClearContents
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

regards


"PeterAtherton" wrote:

Hi
Right-click on the Excel icon at the left of the file toolbar and choose
View Code. This opens the Workbook code sheet. Copy the revised formula into
the code.

Private Sub workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
'sheets("Menu").select
'cells(4,2).select
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Beware the code write the sheets every time you save the workbook. in this
case I think you should insert lines to place the list on a particular sheet
and lines.

I've added a couple of lines to the code to give you the idea; just remove
the rems (apostrophes) if they are suitable

Regards
Peter
"2007-User" wrote:

Thank you so much Peter

How can I make it auto_refreshable in case I add/removed or renamed any
sheets?

Thanks again.


"PeterAtherton" wrote in message
...
HI

Sub listSheets()
s = Sheets.Count
r = ActiveCell.Row: c = ActiveCell.Column
For i = 1 To s
Cells(r, c) = Sheets(i).Name
Cells(r, c + 1) = Sheets(i).Index
r = r + 1
Next
End Sub

Regards
Peter

"2007-User" wrote:

Hi Guys,

I need a VBA to sort all the sheet's names in a column,
Can this list be updated automatically In case I delete any sheets?

Can anyone please direct me to create such VBA?

Thanks in advance.