CODEWITHSUNDEEP
vbaexceldynamic
Steven Gilbert
Steven Gilbert

Reputation: 35

Referencing a dynamic cell in VBA function for creating an Index of Worksheet with that same name

I have 3 worksheets: Table of Contents, Sheet Generator, and Sheet 1.

The "Sheet Generator" worksheet is where the unique sheet name is created, we will call the unique sheet "Sheet 1". At the point of generating "Sheet 1", the macro also inputs the unique name "Sheet 1" into a new line in in "Table of Contents" worksheet.

This process happens many, many times and I can end up with 30 new uniquely named sheets.

Currently I am manually adding a hyperlinks to these sheets so I can jump to each one a little quicker, than having to scroll horizontally for days. I want to add into the macro that generates the unique name into the "Table Of Contents" a hyperlink to that sheet so I no longer have to do it manually.

Doing it manually looks like this when the "Table of Contents" has "Sheet 1" written in cell A3.

Sub TestINDEX2()

Range("A3").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "'Sheet 1'!ac", TextToDisplay:="Sheet 1"

End Sub

The unique name of the sheet is generated in cell "NB2" in the "Sheet Generator" worksheet. But the hyperlink needs to be in cell "A3" in the "Table of Contents" worksheet.

I want it to do this:

Sub TestINDEX2()

Range("A3").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "'Sheet Generator'!NB2", TextToDisplay:="'Sheet Generator'!NB2"

End Sub

But, this obviously doesn't work....

Any suggestions on how to reference a dynamic cell into a macro that adds hyperlinks?

Upvotes: 0

Views: 427

Answers (2)

tigeravatar
tigeravatar

Reputation: 26640

I think this is what you're looking for:

Dim wb As Workbook
Dim wsToC As Worksheet
Dim wsGen As Worksheet
Dim rDest as Range

Set wb = ActiveWorkbook
Set wsToC = wb.Sheets("Table of Contents")
Set wsGen = wb.Sheets("Sheet Generator")
Set rDest = wsToC.Cells(wsToC.Rows.Count, "A").End(xlUp).Offset(1)
If rDest.Row < 3 Then Set rDest = wsToC.Range("A3")

wsToC.Hyperlinks.Add Anchor:=rDest, _
                     Address:="", _
                     SubAddress:="'" & wsGen.Range("NB2").Text & "'!A3", _
                     TextToDisplay:=wsGen.Range("NB2").Text

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

assuming sheets names list is in "Sheet Generator" sheet from cell A3 downwards, you may try this:

Option Explicit

Sub BuildHyperlinks()
    Dim cell As Range
    With Worksheets("Sheet Generator")
        For Each cell In .Range("A3", .Cells(.Rows.Count, 1).End(xlUp))
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            "'" & cell.Value2 & "'!A1", TextToDisplay:=cell.value
        Next
    End With
End Sub

Upvotes: 1

Related Questions