List of all sheets - how?

[Malcolm Hughes]

I want to create a list all of the sheets in an excel file, within the
target file.

Don't know how to do it and will appreciate any guidance.
Thanks in anticipation.
MPH

 

[R..VENKATARAMAN]

use somthing like his sub

Public Sub test()
Dim dest As Range
Set dest = Worksheets("sheet1").Range("a1")
'Dim i As Integer(if necessary use this line)
'not necessary in excel 2002
'dim j as integer=not necessary in excel 2002
j = Worksheets.Count
For i = 1 To j
dest.Value = Worksheets(i).Name
Set dest = dest.Offset(1, 0)
Next
End Sub

 

[EdMac]

Hi,

You could try using ASAP utilities - find via Google - this has th
facilityy of creating an index of all sheets in a workbook and ca
hyperlink to them.

E

 

[Arvi Laanemets]

Hi

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


In a column, you use this UDF to return 1st, 2nd, etc sheet name - like this

A1=IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()))
(and copy down)