how to make a list of all the tab in an excel file
 

I am many tabs in one file and I want to know how many tabs I have and the
name of each tab. How can I do that. I know by right click on the left scroll
bar I can see some, is there a way I can see all the name at one time

how to make a list of all the tab in an excel file
 

First enter this UDF:

Function sheetname(i As Integer) As String
sheetname = ""
If i Sheets.Count Then Exit Function
sheetname = Sheets(i).Name
End Function

then in A1 enter:

=sheetname(row()) and copy down.
--
Gary''s Student - gsnu200790


"RW" wrote:

I am many tabs in one file and I want to know how many tabs I have and the
name of each tab. How can I do that. I know by right click on the left scroll
bar I can see some, is there a way I can see all the name at one time

how to make a list of all the tab in an excel file
 

I am sorry I do not understand your instruction. Do you mind to tell me step
by step please. do I do this in a new excel file ?? or the same file

"Gary''s Student" wrote:

First enter this UDF:

Function sheetname(i As Integer) As String
sheetname = ""
If i Sheets.Count Then Exit Function
sheetname = Sheets(i).Name
End Function

then in A1 enter:

=sheetname(row()) and copy down.
--
Gary''s Student - gsnu200790


"RW" wrote:

I am many tabs in one file and I want to know how many tabs I have and the
name of each tab. How can I do that. I know by right click on the left scroll
bar I can see some, is there a way I can see all the name at one time

how to make a list of all the tab in an excel file
 

This is a User Defined Function (UDF). Here are simple instructions and some
references:

UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

--
Gary''s Student - gsnu200790


"RW" wrote:

I am sorry I do not understand your instruction. Do you mind to tell me step
by step please. do I do this in a new excel file ?? or the same file

"Gary''s Student" wrote:

First enter this UDF:

Function sheetname(i As Integer) As String
sheetname = ""
If i Sheets.Count Then Exit Function
sheetname = Sheets(i).Name
End Function

then in A1 enter:

=sheetname(row()) and copy down.
--
Gary''s Student - gsnu200790


"RW" wrote:

I am many tabs in one file and I want to know how many tabs I have and the
name of each tab. How can I do that. I know by right click on the left scroll
bar I can see some, is there a way I can see all the name at one time

how to make a list of all the tab in an excel file
 

You could use a macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim I As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(I, 0).Value = Sheet.Name
I = I + 1
Next Sheet
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.

To have selectable list of sheets see Debra Dalgleish's site for code to create
a sheet navigation toolbar.

http://www.contextures.on.ca/xlToolbar01.html


Gord Dibben MS Excel MVP


On Sun, 8 Jun 2008 17:07:00 -0700, RW wrote:

I am many tabs in one file and I want to know how many tabs I have and the
name of each tab. How can I do that. I know by right click on the left scroll
bar I can see some, is there a way I can see all the name at one time