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

 

[Mudface]

Add a worksheet to your workbook and name it "Index". Enter this code in the ThisWorkbook module: -<pre>
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim i As Integer
Worksheets("Index").Range("A:A").ClearContents
For Each ws In ThisWorkbook.Worksheets
i = i + 1
Worksheets("Index").Range("A" & i) = ws.Name
Next ws

End Sub</pre>
This message was edited by Mudface on 2002-10-18 17:40

 

[Denise]

Thanks for responding...
I'm afraid I don't have much experience with writing code, so I'm not sure how to specify the ThisWorkbook module.

I tried to create a bogus macro whose code I could over-write, but for some reason I keep getting an "invalid outside procedure" error.

Can you help me through the basics of entering that code?

Thanks again.

Denise

 

[Mudface]

Enter the VBA editor by pressing Alt-F11. On the left hand pane will be a list of your worksheets and an entry called ThisWorkbook. Double click on that and copy and paste in the code from above on the right hand pane. Add a worksheet called "Index" (important, the code will fail if you don't), save your workbook and close and re-open it. You should now have a list of worksheets.

 

[Denise]

It worked! Thanks so much.

Denise

 

[MUSTAFAINE]

How i can use this index with hyperlink

 

[meldoc]

Hello

Do you mean this.

http://www.ozgrid.com/VBA/sheet-index.htm

 

[MUSTAFAINE]

i couldnt understand this example maybe for i m using excel 2007

i dare say i want this index with hyperlinks to worksheets

 

[meldoc]

Yes this should do what you want.
as a test open a new workbook with 3 or 4, worksheets then follow the instructions below.

From Osgrid.
First add a new sheet to the Workbook and call it "Index" (optional). Next right click on the sheet name tab of the "Index" sheet and select "View Code".
In here you should paste the code as shown below.
* Note Use This Code, the code on Osgrid errors where the lines split.*

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:="Back to Index"
                End With
                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
End Sub

Now click the top right X to get back to Excel proper and then Save.
Now, each time you activate the Index sheet the old list will be cleared and a new one will be added.
This ensures the index shown is always up-to date should you add or delete Worksheet.

 

[MUSTAFAINE]

you right meldoc i tried the code, its working too good, thanks for your interesting...