Name of worksheets in one worksheet

[Rasoul Khoshravan]

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.

 

[Rasoul Khoshravan]

I followed your instruction but couldn't get the answer.
In define name dialog windows, there is two boxes, one in top which asks for
name and if I uderstand you correctly, I type "Sheets" here. Shall I type
"Sheet" or real sheet name?
Second box is in buttom and asks for reference cell and I type
"=GET.WORKBOOK(1)", which
sounds strange for me. Anyway I click OK and now in a arbitrary cell I type:
"=Sheets" but I get: GET.WORKBOOK(1)!
I think there is something missing in the steps I perform.
Any comment, is hilghy appreciated.
Please let me what is relation between worksheet name and define name?

1. Choose Insert > Name > Define and type Sheets and then
=GET.WORKBOOK(1) in the refers to box. (If you now type =Sheets in a
cell highlight it and press F9 an array of sheet names is returned in
the format "[WorkbookName]SheetName")

2. To enter the list into a range of horizontal cells, select the cells
and execute the formula =Sheets with Ctrl+Shift+Enter. Then you can
copy and choose pastespecial values transpose in another cell and
replace [*] with nothing to get a vertical list of sheetnames

3. For the last part maybe you mean =COUNTIF(A:A,Sheetnames) where
sheetnames is the vertical list created in step2.

Hope this makes sense!

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.

 

[Rasoul Khoshravan]

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?

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

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.

 

[Lori]

1. Choose Insert > Name > Define and type Sheets and then
=GET.WORKBOOK(1) in the refers to box. (If you now type =Sheets in a
cell highlight it and press F9 an array of sheet names is returned in
the format "[WorkbookName]SheetName")

2. To enter the list into a range of horizontal cells, select the cells
and execute the formula =Sheets with Ctrl+Shift+Enter. Then you can
copy and choose pastespecial values transpose in another cell and
replace [*] with nothing to get a vertical list of sheetnames

3. For the last part maybe you mean =COUNTIF(A:A,Sheetnames) where
sheetnames is the vertical list created in step2.

Hope this makes sense!

 

[Gord Dibben]

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

 

[Gord Dibben]

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

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?

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

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

 

[Rasoul Khoshravan]

Thanks a lot.

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

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?

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"
<[email protected]>
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