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.