what's wrong with this code?

[Jon von der Heyden]

Hi,

I have the following code:

Sub List_Ws()

Dim Ws As Worksheet

    Sheets.Add
    ActiveSheet.Name = "Index"
    With Range("A1")
        .Value = "Sheet Name"
        .Font.bold = True
    End With

For Each Ws In ActiveWorkbook.Worksheets

Sheets("Index").Range("A65536").Select
Selection.End(xlUp).Select
With ActiveCell
    .Offset(1, 0).Value = Ws.Name
    .Hyperlinks.Add Anchor:=Selection, Address:="", _
        SubAddress:="'" & Ws.Name & "'!A1", TextToDisplay:=Ws.Name
End With

Next Ws

End Sub

Basically this gives me an index of the sheet names in the active workbook. It worked fine until I tried hyperlinking the sheet names to their corresponding sheets. It's overriding the title "Sheet Name" in A1 with "Index". Any ideas why?

Thanks,
Jon

 

[Norie]

Jon

Try this.

Sub List_Ws()

Dim Ws As Worksheet

    Sheets.Add
    ActiveSheet.Name = "Index"
    With Range("A1")
        .Value = "Sheet Name"
        .Font.Bold = True
    End With

For Each Ws In ActiveWorkbook.Worksheets

Sheets("Index").Range("A65536").Select
Selection.End(xlUp).Select
With ActiveCell.Offset(1, 0)
    .Value = Ws.Name
    .Hyperlinks.Add Anchor:=ActiveCell.Offset(1, 0), Address:="", _
        SubAddress:="'" & Ws.Name & "'!A1", TextToDisplay:=Ws.Name
End With

Next Ws

End Sub

Or this.

Sub List_Ws()

Dim Ws As Worksheet
Dim I As Long
    Sheets.Add
    ActiveSheet.Name = "Index"
    With Range("A1")
        .Value = "Sheet Name"
        .Font.Bold = True
    End With
    
    I = 2
    
    For Each Ws In ActiveWorkbook.Worksheets

        With Sheets("Index").Range("A" & I)
            .Value = Ws.Name
            .Hyperlinks.Add Anchor:=Sheets("Index").Range("A" & I), Address:="", _
        SubAddress:="'" & Ws.Name & "'!A1", TextToDisplay:=Ws.Name
        End With
        I = I + 1
    Next Ws

End Sub

 

[Nimrod]

Sub List_WsVer2()
Dim Rw As Integer
Dim Ws As Worksheet

    Sheets.Add
    ActiveSheet.Name = "Index"
    With Range("A1")
        .Value = "Sheet Name"
        .Font.Bold = True
    End With
Rw = 1
For Each Ws In ActiveWorkbook.Worksheets
    If Not Ws.Name = ActiveSheet.Name Then
    Rw = Rw + 1
    
    With Cells(Rw, 1)
        .Value = Ws.Name
        .Hyperlinks.Add Anchor:=Cells(Rw, 1), Address:="", _
            SubAddress:="'" & Ws.Name & "'!A1", TextToDisplay:=Ws.Name
    End With
    End If
Next Ws

End Sub

BIG TIP... wien yourself off using "selects" .. :wink:

 

[Jon von der Heyden]

Beautiful! Thankyou both.