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.