Name of worksheets in one worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rasoul Khoshravan - 19 Oct 2006 11:23 GMT

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.

Reply to this Message

Lori - 19 Oct 2006 17:30 GMT

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.
[quoted text clipped - 5 lines]
> Finally I want to perform: counta("sheetname",A:A). If there is any easy way
> to do this command, let me know.

Reply to this Message

Rasoul Khoshravan - 19 Oct 2006 15:58 GMT

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
[quoted text clipped - 21 lines]
>> way
>> to do this command, let me know.

Reply to this Message

Gord Dibben - 19 Oct 2006 18:49 GMT

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.
[quoted text clipped - 5 lines]
>Finally I want to perform: counta("sheetname",A:A). If there is any easy way
>to do this command, let me know.

Reply to this Message

Rasoul Khoshravan - 19 Oct 2006 16:17 GMT

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
[quoted text clipped - 23 lines]
>>way
>>to do this command, let me know.

Reply to this Message

Gord Dibben - 19 Oct 2006 23:23 GMT

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
[quoted text clipped - 34 lines]
>>>way
>>>to do this command, let me know.

Gord Dibben MS Excel MVP

Reply to this Message

Rasoul Khoshravan - 20 Oct 2006 04:11 GMT

Thanks a lot.

> Just add a new sheet......here is amended code.
>
[quoted text clipped - 84 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message