Index Worksheets

Denise

New Member
Joined
Feb 19, 2002
Messages
18
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

New Member
Joined
Feb 19, 2002
Messages
18
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

New Member
Joined
Feb 19, 2002
Messages
18

ADVERTISEMENT

It worked! Thanks so much.

Denise
 

MUSTAFAINE

New Member
Joined
Dec 21, 2009
Messages
34
i couldnt understand this example maybe for i m using excel 2007

i dare say i want this index with hyperlinks to worksheets
 

meldoc

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
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.*

Code:
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

New Member
Joined
Dec 21, 2009
Messages
34
you right meldoc i tried the code, its working too good, thanks for your interesting...
 

Forum statistics

Threads
1,148,171
Messages
5,745,173
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top