在excel中创建sheet的索引 Use index sheets to quickly navigate in an Excel workbook

1. 在最前面创建一个新的sheet

2. 右击sheet,选择 view code

3. 输入下面的代码

4. Alt + Q 返回并保存excel

原文来自: Use index sheets to quickly navigate in an Excel workbook

http://www.techrepublic.com/article/use-index-sheets-to-quickly-navigate-in-an-excel-workbook/5629983

By Mary Ann Richardson
March 22, 2005, 4:40pm PST

Whenworking with large workbooks in Microsoft Excel, you can spend a good deal oftime navigating through dozens of sheet name tabs to find the one you need. Onemethod for making this task easier is to build an index sheet that containshyperlinks to every worksheet in your workbook.

Tocreate the index, follow these steps:

1.       Inserta new worksheet at the beginning of your workbook and rename it Index.

2.       Right-clickon the sheet tab and select View Code.

3.       Enterthe following code below

4.       Press[Alt][Q] and save the workbook.

Thenext time you open the workbook, the Index sheet will contain a list ofhyperlinks to each worksheet in the workbook. Each worksheet also contains ahyperlink back to the Index sheet. In this example, the link back to the Indexis contained in cell H1, but it can be in any cell that is blank for allworksheets.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 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
        M = M + 1
        With wSheet
            .Range("H1").Name = "Start" & wSheet.Index
            .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
        End With
            Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
        Next wSheet
End Sub