Hyperlinks Subaddress = variable worksheet (VBA)

[bukimi]

Hello!

I'm struggling to get it right, but whatever I try, I can't make it work.

I have a table in which I need to make every cell in first column as a link to a worksheet of the same name that is inside the cell.
(that is: cells in 1st column is 1:1 a list of worksheets, excluding worksheet #1 which is a table of contents named "Zestawienie" with some details)

I tried this one:

Dim wZestawieniu As Integer
Dim ArkuszDocelowy As Range


wZestawieniu = 2


Do Until Cells(wZestawieniu, 1).Value = ""
Set ArkuszDocelowy = Worksheets(wZestawieniu).Cells(1, 1)
Sheets("ZESTAWIENIE").Hyperlinks.Add Anchor:=Cells(wZestawieniu, 1), Address:="", SubAddress:=ArkuszDocelowy
wZestawieniu = wZestawieniu + 1
Loop

but get error when hyperlink is about to be added.

This one doesn't work either:

Dim WS As Worksheet
Dim wZestawieniu As Integer
Dim ArkuszDocelowy As Range

For Each WS In ActiveWorkbook.Worksheets
Sheets("ZESTAWIENIE").Hyperlinks.Add Anchor:=Cells(wZestawieniu, 1), Address:="", SubAddress:=Chr(34) & Chr(39) & WS.Name & Chr(39) & "A1" & Chr(34)
wZestawieniu = wZestawieniu + 1
Next

I don't know how to put variable WorkSheet name as subaddress for hyperlink inside a cell.
As far as I know Anchor tells VBA which cell will be an active link, Address is required only if it's external link (empty if not) and Subaddress is a name of target worksheet and range (presumably A1).

This code recorded by macro recorder obviously works, but it has constant worksheet name here (BodyFit), which I need to be a variable:

Range("A3").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'Bodyfit'!A1"

Thank you in advance for all your help!

 

[steve the fish]

So you have a table that has sheet names in its first column and you want to convert those cells to be hyperlinks to cell A1 of the sheet name?

 

[bukimi]

So you have a table that has sheet names in its first column and you want to convert those cells to be hyperlinks to cell A1 of the sheet name?

Yes, but without changing the text inside that cell. Final result should be a clickable sheet name within a cell (by default hightlighted in blue) that directs user to that sheet (cell A1 or wherever in it).

 

[steve the fish]

Something like this perhaps:

Dim sh As Worksheet, ws As Worksheet
Dim lr As Long, a As Long
Dim arr As Variant
Dim c As Range

Set sh = Sheets("Table") 'change to suit
lr = sh.Range("A" & Rows.Count).End(xlUp).Row

ReDim arr(0 To Worksheets.Count - 1)
For Each ws In ThisWorkbook.Worksheets
    arr(a) = ws.Name
    a = a + 1
Next

For Each c In sh.Range("A1:A" & lr) 'change to suit
    If Len(c.Value) > 0 Then
        myMatch = Application.Match(c.Value, arr, 0)
        If Not IsError(myMatch) Then
            sh.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:="'" & c.Value & "'!A1"
        End If
    End If
Next

 

[bukimi]

That line

[COLOR=#333333]sh.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:="'" & c.Value & "'!A1"[/COLOR]

gave me exactly what I needed. I couldn't grasp how to add these ' symbols and !A1 and put worksheetname from variable there too.

Finally my working code looks like that:

Dim wZestawieniu As Integer


wZestawieniu = 2


Do Until Cells(wZestawieniu, 1).Value = ""
Sheets("ZESTAWIENIE").Hyperlinks.Add Anchor:=Cells(wZestawieniu, 1), Address:="", SubAddress:="'" & Worksheets(wZestawieniu).Name & "'!A1"
wZestawieniu = wZestawieniu + 1
Loop

I didn't mention that worksheets are in the same order in the file and on the list, so simple match by worksheet number in order is enough. I dropped that name matching part for cases where someone would make a small mistake in the table or worksheet name (no match then).
"Do until" empty value also is enough to stop code when list is over

Thank you very much. You helped me a lot!