Just add a new sheet......here is amended code.

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
Worksheets.Add
For i = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(i)
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = ws.CodeName
End With
Next i
End Sub

To get them in reverse order is beyond me currently, but I will work on it and
hope someone else jumps in and saves me the brain-drain.

Codename is how Excel numbers your sheet, which can be different than your
actual sheetnames.

This is how Excel keeps track of sheets.

ALT + F11 to open VB Editor.

Find your workbook/project and expand Excel Objects.

You will see the codenames and sheetnames.

If you want a list without the codesnames use this macro.

Private Sub ListSheets()
Dim rng As Range
Dim i As Integer
Worksheets.Add
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord


On Fri, 20 Oct 2006 00:17:38 +0900, "Rasoul Khoshravan"
wrote:

Thanks it worked and it is very nice. Only two minor questions:
I note that this MAcro makes the list in the first worksheet. So if I have
data on it, they will be over written.
If I want to have the list of sheets in a sheet other than 1st one, how
should I amend the MAcro?

2- Also the sequense of names is from 1st to last sheet. IS it possible to
have them reverse (from last to first)?

PS) What is CodeName?

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(i)
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = ws.CodeName
End With
Next i
End Sub


Gord Dibben MS Excel MVP

On Thu, 19 Oct 2006 19:23:27 +0900, "Rasoul Khoshravan"

wrote:

I know how to obtain name of worksheets in the same worksheet.
Following function will do this.
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

I want to obtain name of all worksheets in one sheet. How can I do it.
Sheet names are not: [sheet]+numbers.

Finally I want to perform: counta("sheetname",A:A). If there is any easy
way
to do this command, let me know.


Gord Dibben MS Excel MVP