Indexing Macro with Hyperlink

[JVID]

Hey All,

Using 2007 Excel-

This macro is not working. It makes an index of every single sheet then hyper links it on the index.

Does anyone have some insight or a better code?

Private Sub indexer()
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

Thanks,

John

 

[JoeMo]

Can you be more specific about what is not working?

 

[JVID]

Sorry- it's saying invalid use of "Me" keyword

 

[JoeMo]

Sorry- it's saying invalid use of "Me" keyword

What you have apparently adapted is intended to be a worksheet_activate event macro that goes in a module for the Index worksheet - NOT a standard module. You can then properly refer to the sheet as Me.

 

[JVID]

hmmm any ideas on how to get it to work? Change the name?

 

[JoeMo]

hmmm any ideas on how to get it to work? Change the name?

Put everything between the first line (Sub ..) and last line (End Sub) into a worksheet_activate event module for the index worksheet.

 

[JVID]

Sorry Joe- I can not follow this. How do I do this?

 

[JVID]

Nevermind I got it! Thanks!

 

[RobertCotton]

I am trying to do the exact same thing that is described in this post. However, I do not understand the "Put everything between the first line (Sub ..) and last line (End Sub) into a worksheet_activate event module for the index worksheet." part of the reply. (Apparently the original poster "got it". Can anyone help me figure out how to use this to populate an "Index" worksheet with a hyperlink to each additional worksheet in the workbook?

Does this macro actually create the "Index" worksheet or simply populate it?

Thanks

 

[JoeMo]

I am trying to do the exact same thing that is described in this post. However, I do not understand the "Put everything between the first line (Sub ..) and last line (End Sub) into a worksheet_activate event module for the index worksheet." part of the reply. (Apparently the original poster "got it". Can anyone help me figure out how to use this to populate an "Index" worksheet with a hyperlink to each additional worksheet in the workbook?

Does this macro actually create the "Index" worksheet or simply populate it?

Thanks

Create a new sheet in your workbook and name it "Index". Then install the code below. This will place links in cell A1 in every sheet (or subsequently added sheets). If you want the links in a different cell change the A1 to that cell (2 places in the code).
To install the code:
1. Right-click the worksheet you want to apply it to (Index sheet) and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long

calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False
n = 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
            n = n + 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(n, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
Application.Calculation = calcState
Application.ScreenUpdating = scrUpdateState
End Sub