CODEWITHSUNDEEP
vbaexcel
user3033933
user3033933

Reputation: 57

Indexing in Excel using VBA

The below code is used in My workbook for Indexing. Problem is this code is overwriting the cells in A1 with "Back to Index". How can I modify this code so it does not overwrite the current value A1 cell in each sheet and a Hyperlink in A1 cell with current value retained is created back to the Index page? TIA

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

Upvotes: 2

Views: 224

Answers (1)

user4039065
user4039065

Reputation:

Seems pretty straightforward to me but perhaps I did not understand the problem.

    With wSheet
        .Range("A1").Name = "Start_" & .Index
        .Hyperlinks.Add Anchor:=.Range("A1"), Address:=vbNullstring, _
        SubAddress:="Index", TextToDisplay:=.Range("A1").Text
    End With

Upvotes: 1

Related Questions