Automatically pulling sheet names

  • Thread starter Thread starter Chad Somerset
  • Start date Start date
C

Chad Somerset

Is there a way to automatically pull the names of my
worksheets into an index sheets? I have a workbook with
about 100 worksheets and I want to create an index of the
sheets.

Thanks.
 
Hi

This is VBA code so hold down ALT-F11 and go to Insert>Module.
In the blank module paste the following code:

*********************************************
Public Sub IndexSheets()

Dim i As Integer

For i = 1 To Sheets.Count
ActiveCell.Offset(i+1, 0).Value = Sheets(i).Name
Next i

End Sub
*********************************************

Then save and close. In the Excel window click any where on the sheet
like A1 for example and go to Tools>Macro>Run Macro.
Click on IndexSheets and click Run.
This should provide a list of all the Sheets names row by row.

Hope this helps!

Tom
 
Chad,

This macro will create a list of the sheetnames. Copy it into a VB module
for your workbook.

Sub ListPageName()
Dim SheetName As String
Dim SheetCount As Integer
Dim i As Integer
SheetCount = ActiveWorkbook.Sheets.Count
For i = 1 To SheetCount
SheetName = ActiveWorkbook.Sheets(i).Name
ActiveCell.Offset(i - 1, 0).FormulaR1C1 = SheetName
Next i
End Sub

PC
 
Back
Top