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]

You are welcome.

 

[Legacy 194999]

EXCELLENT!!...Helped me w/ the Index thing AND how to correctly enter a code...been fighting with that for so long! THANKS!

 

[Legacy 194999]

"SubAddress:="Index", TextToDisplay:="Back to Index"

How do I change this part of the code so that it doesn't change my text in A1 to "Back to Index". I like the hyperlink back to the index page, but I want my A1 cell text to remain what I have in there and be changed. I've tried but can't seem to get it.

 

[meldoc]

Hello

Try this, note however that if cell A1 on any sheet is left blank, "Hyperlinks.Add" will automatically add "Index" as the friendly name.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    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
                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
End Sub

 

[Legacy 194999]

Thank you so much! Works perfectly!!

 

[meldoc]

Hello weaselmcguff,

Thank you for your PM. I have chosen to reply through the message board so that the answer, if it is of any value, is available to all.

Hi,

I saw your post on Mrexcel and had a question on it.

http://www.mrexcel.com/forum/showthread.php?24957-Index-Worksheets

the last comment where you set it up and creates and Index sheet of all the work sheets in the work book. Is there a way to apply a description to these sheets that it pulls?

What I want to do is take the listing of the sheets which it places in column A and then in Column B it apply the description to that sheet. Is this possible? Each sheet has difffernt description and sheets are constantly added and deleted. The orginal version of creating and hyperlinking to them works great. Just like to be able to have a description to what each is.

Thanks for your time

Try this, type a description into cell B1, on each sheet you wish to index, or to use another cell just change the reference below.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    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
                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
                Me.Cells(l, 2) = wSheet.Range("[COLOR="#FF0000"]B1[/COLOR]")
        End If
    Next wSheet
End Sub

 

[SeraphicSoul]

any way to do this without macros?

 

[meldoc]

Hello

Maybe this, I just found this by searching, I've not tried it yet, it's late here I'll try tomorrow.

http://www.mrexcel.com/forum/showth...t-names-using-a-formula&p=2118912#post2118912

 

[SeraphicSoul]

This post says right on it that it uses macros. Oh well.... anyone else that might have insight?

 

[lapta301]

Brian

I have found this thread which is unusual in that it includes the ability to have a description on each sheet which is wonderful.

Would it be possible to include in column C the Internal Sheet Name and in column D the worksheet Index Number.

I think that with these extra fields we would have virtually all sheet info in one place.

My grateful thanks for any help that you can provide.