I am guessing taht you want to fill a range of cells with the names of all of
the worksheets in the book? If that is the case then I would be inclined to
use a button or some event such as sheet activate to re-populate the list. I
guess the big question is what do you want the list for? Place this in a
standard code module and call it whenver you want to repopulate the list...

sub AddSheetNames
dim wks as worksheet
dim rng as range

set rng = sheets("sheet1").range("A1") 'Change this
for each wks in worksheets
rng = wks.name
set rng = rng.offset(1,0)
next wks
end sub

--
HTH...

Jim Thomlinson


"carl" wrote:

Thanks Jim. Still cannot get this to work: Formula returns #Name?.

Ayy thoughts or suggestions on another method ?

"Jim Thomlinson" wrote:

I think you want Shift + Ctrl + Enter... IMO this is not a great UDF as it
will not be dynamic as sheets are added or removed, but it will work in a
pinch...
--
HTH...

Jim Thomlinson


"carl" wrote:

Thanks. When I get to this step:


8) WITH THEM STILL ALL SELECTED, type =AllSheetNames(), and press
<ALT<ENTER

When I press <ALT<ENTER, nothing happens.



"Allllen" wrote:

It works fine. Here is how to do it with a new blank workbook:

First, you need to insert that code in a module:

1) Tools Macro VB Editor
2) In the VBE, make sure you can see the project explorer (View Project
Explorer)
3) Find the project that looks like your book ("VBAProject (Book1)") and
right click on it
4) Insert Module
5) You will see a blank page on the right hand side. Copy the text and put
it in there

Public Function AllSheetNames()
Dim Arr() As String
Dim I As Integer
ReDim Arr(Sheets.Count - 1)
For I = 0 To Sheets.Count - 1
Arr(I) = Sheets(I + 1).Name
Next I
AllSheetNames = Arr ' return a row array OR
AllSheetNames = Application.WorksheetFunction.Transpose(Arr)

End Function

6) Close the VB Editor and go back to Excel.

7) Select cells a1:a3 (for example)

8) WITH THEM STILL ALL SELECTED, type =AllSheetNames(), and press <ALT<ENTER

You will see that excel writes them all for you in cells a1:a3.
HTH

please rate
--
Allllen


"carl" wrote:

I was pointed to this macro:

Public Function AllSheetNames()
Dim Arr() As String
Dim I As Integer
ReDim Arr(Sheets.Count - 1)
For I = 0 To Sheets.Count - 1
Arr(I) = Sheets(I + 1).Name
Next I
AllSheetNames = Arr ' return a row array OR
AllSheetNames = Application.WorksheetFunction.Transpose(Arr)
' return a column array
End Function

I can't seem to get it to work in my workbook.

Can someone walk me through the steps.

Thank you in advance.