Auto Worksheet index

[Pearso]

Hi all

yesterday i had some help with a macro to create a list of references. I would like to take this one step further an make each name in the list a hyperlink to the worksheet. The code for the hyper link i con't seem to get it to work. Does anyone have any ideas

Public Sub ListWrkShts()
 
    Dim i As Integer
    Dim x As Integer
    Dim sSheet As String
    Dim oWB As Workbook
    
    Range("B1").Value = "Worksheets List"
 
    x = 2
    Set oWB = ActiveWorkbook
    For i = 2 To oWB.Sheets.Count
        oWB.Sheets(1).Cells(x, 2).Value = oWB.Sheets(i).Name
        ActiveSheet.Hyperlinks.Add Anchor:=oWB.Sheets(1).Cells(x, 2),   Address:="", SubAddress:= _
        oWB.Sheets(i).Name, TextToDisplay:= _
        oWB.Sheets(i).Name
        x = x + 1
    Next
    Set oWB = Nothing
    
    Sort
    Range("A1").Select
    
End Sub

Can anyone help

[RobDog888]

I fixed it for you

Public Sub ListWrkShts()
 
    Dim i As Integer
    Dim x As Integer
    Dim sSheet As String
    Dim oWB As Workbook
    
    Range("B1").Value = "Worksheets List"
 
    x = 2
    Set oWB = ActiveWorkbook
    For i = 2 To oWB.Sheets.Count
        oWB.Sheets(1).Cells(x, 2).Value = oWB.Sheets(i).Name
        Workbooks(1).Sheets(1).Hyperlinks.Add oWB.Sheets(1).Cells(x, 2), "http://www.vbforums.com", oWB.Sheets(i).Name, oWB.Sheets(i).Name
        x = x + 1
    Next
    Set oWB = Nothing
    
    Sort
    Range("A1").Select
    
End Sub

[Pearso]

Yes Very good RobDog, You playing tricks on me all the links open up the vbforum. i've tried playing around with it but still can't get it to work like i want it to.

[RobDog888]

Change the "http://www.vbforums.com" parameter to whereever you need (cell, worksheet, website, etc).

[Pearso]

This is what i tried

Workbooks(1).Sheets(1).Hyperlinks.Add oWB.Sheets(1).Cells(x, 2), "oWB.Sheets(i).Name", oWB.Sheets(i).Name, oWB.Sheets(i).Name

And when i click on a link it comes up with "can not open the specified file".

I'm not sure what to do next????


What i want to do is create a list of worksheet names (which i can do) turn them into hyperlinks to the worksheet they resprent and then sort them into abc order.

[RobDog888]

Do it without the double quotes around the object.

Workbooks(1).Sheets(1).Hyperlinks.Add oWB.Sheets(1).Cells(x, 2), oWB.Sheets(i).Name, oWB.Sheets(i).Name, oWB.Sheets(i).Name