How to get Sheet Names on Summary Page

September 30, 2010 at 21:19:43
Specs: Windows XP
Hi,

I've been trying to solve this problem forever. Is there a formula or macro to find all the names of worksheets in an Excel file?

I know how to get sheet names on the page itself.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

But not unto other pages. So I want to pull all the sheet names unto a Summary page.

Also if possible, the solution should be able to adjust for additions to new tabs.

Thanks for your help, I really appreciate it.


See More: How to get Sheet Names on Summary Page

Report •


#1
October 1, 2010 at 04:02:08
1 - Name a sheet "Index"
2 - Run this once to put the names of the current sheets in Column A:

Option Explicit
Sub SheetNames()
Dim sht As Integer
  For sht = 1 To Sheets.Count
    Sheets("Index").Cells(sht, 1).Value = Sheets(sht).Name
  Next sht
End Sub

2 - Place this in the ThisWorkbook module so the list will be update automatically whenever a new sheet or sheets is added:

Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim sht As Integer
  For sht = 1 To Sheets.Count
    Sheets("Index").Cells(sht, 1).Value = Sheets(sht).Name
  Next sht
End Sub

Or just use the Workbook_NewSheet code and add a new sheet to force it to run.


Report •

#2
October 1, 2010 at 04:16:22
Thanks for your response DerbyDad03,

I'm really new to VBA so please bear with me. How do I output the data?

Andy


Report •

#3
October 1, 2010 at 05:08:01
You don't output the data, the code does.

The code will automatically write the data to a sheet called Index, starting in A1.

You need to create a sheet named Index so that the code can write the data.


Report •

Related Solutions

#4
October 3, 2010 at 21:17:04
My code currently stands at:

Private Sub Workbook_Open()
Application.Calculation = xlAutomatic
Application.CalculateBeforeSave = False
Application.Iteration = True
Application. MaxIterations = 1000
Application. MaxChange = .001

Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim sht As Integer
For sht = 1 To Sheets.Count
Sheets("Index").Cells(sht, 1).Value = Sheets(sht).Name
Next sht

End Sub

But there is repeated a Compile Error - Expected End Sub.

Thanks for all your help!


Report •

#5
October 3, 2010 at 21:44:46
You've got an incomplete Private Sub Workbook_Open() macro.

There needs to be an End Sub for that section of code.

For every

Sub procedure_name()

there has to be an

End Sub


Report •

#6
October 3, 2010 at 22:02:23
Private Sub Workbook_Open()
Application.Calculation = xlAutomatic
Application.CalculateBeforeSave = False
Application.Iteration = True
Application. MaxIterations = 1000
Application. MaxChange = .001

End Sub

Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim sht As Integer
For sht = 1 To Sheets.Count
Sheets("Index").Cells(sht, 1).Value = Sheets(sht).Name
Next sht

End Sub

Gives me a compilation error.

Only comments may appear after End Sub, End Function, or End Property.


Report •

#7
October 4, 2010 at 00:04:47
Got it. Thanks for your help DerbyDad!

Report •


Ask Question