Index Worksheets

[Denise]

I'm trying to keep better documentation on some very large workbooks.

As part of this I'd like to create a template that includes a worksheet that lists every other worksheet in the workbook, or a macro that creates a new worksheet and lists every other worksheet in the workbook.

Is this possible?

Hope that made sense, and thanks for any help.

Denise

 

[meldoc]

Hello lapta301

Try.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
Application.ScreenUpdating = False
    With Me
        .Columns.ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
        .Cells(l, 2) = "DESCRIPTION"
        .Cells(l, 3) = "CODE NAME"
        .Cells(l, 4) = "INDEX NO: "
      For Each wSheet In Worksheets
          If wSheet.Name <> Me.Name Then
              l = l + 1
                  With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                      SubAddress:="Index", TextToDisplay:=.Range("A1").Text
                  End With
                  .Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                    SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
                  .Cells(l, 2) = wSheet.Range("B1")
                  .Cells(l, 3) = wSheet.CodeName
                  .Cells(l, 4) = wSheet.Index
                  .Columns.AutoFit
            End If
      Next wSheet
    End With
 Application.ScreenUpdating = True
End Sub

 

[lapta301]

Brian

Works a treat thank you so much.

Regards

Steve