software

I have a workbook with 26 worksheets in it, and on one page I wish for cells
to reference the names on the worksheet tabs.

Here's a macro to create a Table of Contents...

'/==============================================/
Public Sub WorkBookTableOfContents()
'Create a separate worksheet with the name of each sheet
' in the workbook as a hyperlink to that sheet -
' i.e. a Table Of Contents
'07/25/2000 - allow for chart sheets
'08/11/2005 - add Protect/Unprotect information
Dim iRow As Integer, iColumn As Integer, y As Integer
Dim i As Integer, x As Integer, iSheets As Integer
Dim objOutputArea As Object
Dim strTableName As String, strSheetName As String
Dim strOrigCalcStatus As String

strTableName = quot;Table_of_Contentsquot;

'check for an active workbook
If ActiveWorkbook Is Nothing Then 'no workbooks open, so create one
Workbooks.Add
End If

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'Check for duplicate Sheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If Windows.Count = 0 Then Exit Sub
If UCase(Sheets(x).name) = UCase(strTableName) Then
Sheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
'turn warning messages off
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
'turn warning messages on
Application.DisplayAlerts = True
Exit For
End If
Next

'Add new sheet at end of workbook
' where results will be located
Sheets.Add.Move Befo=Sheets(1)

'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.name = strTableName
ActiveWorkbook.ActiveSheet.Range(quot;A1quot;).value = _
quot;Worksheet (hyperlink)quot;
ActiveWorkbook.ActiveSheet.Range(quot;B1quot;).value = _
quot;Visible / Hiddenquot;
ActiveWorkbook.ActiveSheet.Range(quot;C1quot;).value = _
quot;Prot / Unquot;
ActiveWorkbook.ActiveSheet.Range(quot;D1quot;).value = _
quot; Notes: quot;

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'Initialize row and column counts for putting
' info into StrTableName sheet
iRow = 1
iColumn = 0

Set objOutputArea = _
ActiveWorkbook.Sheets(strTableName).Range(quot;A1quot;)

'Check Sheet names
For x = 1 To iSheets
strSheetName = Sheets(x).name
'put information into StrTableName worksheet
With objOutputArea
If strSheetName lt;gt; strTableName Then
.Offset(iRow, iColumn) = quot; quot; amp; strSheetName
If UCase(TypeName(Sheets(x))) lt;gt; quot;CHARTquot; Then
Sheets(x).Hyperlinks.Add _
Anchor:=objOutputArea.Offset(iRow, _
iColumn), _
Address:=quot;quot;, SubAddress:=Chr(39) amp; _
strSheetName amp; Chr(39) amp; quot;!A1quot;
End If
If Sheets(x).Visible = True Then
.Offset(iRow, iColumn 1) = quot; Visiblequot;
.Offset(iRow, iColumn).Font.Bold = True
.Offset(iRow, iColumn 1).Font.Bold = True
Else
.Offset(iRow, iColumn 1) = quot; Hiddenquot;
End If
If Sheets(x).ProtectContents = True Then
.Offset(iRow, iColumn 2) = quot; Pquot;
Else
.Offset(iRow, iColumn 2) = quot; Uquot;
End If
iRow = iRow 1
End If
End With
Next x

Sheets(strTableName).Activate

'make comment
Range(quot;C1quot;).AddComment

With Range(quot;C1quot;).Comment
.Visible = False

.Text Text:= _
quot;Protected / Unprotected Worksheetquot;

End With

'format worksheet
Range(quot;Aquot;).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.name = quot;Tahomaquot;
'.FontStyle = quot;Regularquot;
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
'.ColorIndex = xlAutomatic
End With

Range(quot;A2quot;).Select
ActiveWindow.FreezePanes = True
Range(quot;A1quot;).Font.Bold = True
Columns(quot;Aquot;).EntireColumn.AutoFit

Range(quot;A11quot;).Select
With Selection
.HorizontalAlignment = xlCenter
.Font.Underline = xlUnderlineStyleSingle
End With

Range(quot;B1quot;).Select
With ActiveCell.Characters(Start:=1, Length:=7).Font
.FontStyle = quot;Boldquot;
End With
With ActiveCell.Characters(Start:=8, Length:=9).Font
.FontStyle = quot;Regularquot;
End With

Columns(quot;Aquot;).EntireColumn.AutoFit
Range(quot;A11quot;).Font.Underline = _
xlUnderlineStyleSingleAccounting

Range(quot;B:Bquot;).HorizontalAlignment = xlCenter

Range(quot;C1quot;).WrapText = True
Columns(quot;C:Cquot;).HorizontalAlignment = xlCenter
Rows(quot;1:1quot;).RowHeight = 100
Columns(quot;C:Cquot;).ColumnWidth = 5.15
Rows(quot;1:1quot;).EntireRow.AutoFit

Range(quot;D1quot;).HorizontalAlignment = xlLeft
Columns(quot;Dquot;).ColumnWidth = 65

Range(quot;B1quot;).Select

Selection.AutoFilter

Application.Dialogs(xlDialogWorkbookName).Show

End Sub
'/==============================================/
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.quot;trtfnquot; wrote:

gt; I have a workbook with 26 worksheets in it, and on one page I wish for cells
gt; to reference the names on the worksheet tabs.

How have the worksheets been named?

Unique names or just Sheet1, Sheet2 etc?

If Sheet1 etc. a formula

=INDIRECT(quot;Sheetquot; amp; (ROW()-1) amp; quot;!E3quot;)

Gives Sheet1!E3. When copied down, A2 will be Sheet2!E3, etc.

If unique, you will have to get the names into a list on one sheet and reference
them from that list.

To list the sheets into a sheet in Column A

Best to insert a new worksheet then run the macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Set Rng = Range(quot;A1quot;)
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i 1
Next Sheet
End SubGord Dibben Excel MVP
On Thu, 26 Jan 2006 08:16:04 -0800, quot;trtfnquot; gt;
wrote:

gt;I have a workbook with 26 worksheets in it, and on one page I wish for cells
gt;to reference the names on the worksheet tabs.

Gord Dibben MS Excel MVP