how do I make a list of the names of worksheets in an excel workb

[Peo Sjoblom]

One way

http://www.cpearson.com/excel/excelM.htm


at the bottom called sheet names, this is by using VBA, no built in function
for thus


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Arvi Laanemets]

Hi

Another option is an user defined function. The advantage is that whenever
you add a sheet, or change the order of them in workbook, the list is
corrected automatically. The disandvantage ... ok, less formulas is always a
possibility to be considered.

Create an UDF (Activate VBA editor pressing Alt+F11, insert a new module
when there is no one in your workbook, and copy the code below into it).

-----

Public Function TabI(TabIndex As Integer, Optional parVolatile As Date) As
String
TabI = Sheets(TabIndex).Name
End Function

-----

On sheet you want to have the list of sheets in, into cell A1 enter the
formula
=IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()))
, and copy it down for some number of rows.

In case you want a heading for sheets list in cell A1, enter the modified
formula into cell A2
=IF(ISERROR(TABI(ROW()-1,NOW())),"",TABI(ROW()-1))
, and again copy it down.