2

I am trying to create VBA code that identifies named ranges in my workbook and creates a hyperlink for each. These hyperlinks need to be capable of being copied to other documents.

I found two examples--each one does half of the job, but am having trouble combining them:

1) Find and list ranges:

Sub namedranges()

For Each n In ThisWorkbook.Names
    i = i + 1
    Range("a" & i + 1) = n.Name
Next n

End Sub

2) Create Hyperlinks of all worksheets:

Sub CreateLinksToAllSheets()

Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
    If ActiveSheet.Name <> sh.Name Then
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="",   SubAddress:="'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
        ActiveCell.Offset(1, 0).Select
    End If
Next sh

End Sub

When I combine, as follows, I get an error that object/variable not set.

Sub hyperlinknamedranges()

Dim sh as worksheet
Dim cell as range
For Each n in Thisworkbook.names
    ' ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="",    SubAddress:="'" & sh.Name & "'" & "#" & "'" & n.name & "'", 
    ActiveCell.Offset(1, 0).Select
next n

End Sub

The final code does not need to produce a listing of ranges, as is done in the first example.

Community
  • 1
  • 1
Learner
  • 21
  • 2

2 Answers2

0

I think you are after something like the code below, it will add in each cell in Column "A" (starting from row 2), the name of the Named Range, and it link to it once you click on the Hyperlink.

Code

Option Explicit

Sub namedranges()

Dim n As Name
Dim i As Long

i = 2
For Each n In ThisWorkbook.Names
    Range("A" & i).Hyperlinks.Add Anchor:=Range("A" & i), Address:="", SubAddress:=n.RefersTo, TextToDisplay:=n.Name
    i = i + 1
Next n

End Sub
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • This is very close to what I am looking for--thanks for your response. If I copy the hyperlink from the source file to a target file, the hyperlink will then direct me to a similar range on the target file, not the original named range on the source file. Any ideas on how to hyperlink back to the original named range on the source workbook? – Learner Apr 27 '17 at 18:19
  • @Learner not sure I understand, is this code suppose to be directed to Named Ranges in another workbook ? – Shai Rado Apr 27 '17 at 18:23
  • Hi. I would like the code to refer to the original named range. If that range resides at FileA.xlsm#NamedRange, when I copy the hyperlink from FileA.xlsm to FileB.xlsm, I would like it to refer to the named range in FileA. – Learner Apr 27 '17 at 18:32
  • Hi. Thanks for your help. I added a string to name the workbook and path, then inserted it in the hyperlink statement. Works like a champ. – Learner Apr 28 '17 at 01:07
0
Sheet2.Select

Range("a1").Select

Dim nm As Name

  For Each nm In Names

    ActiveCell.Value = nm.Name

    ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:= _
    nm.RefersTo

    ActiveCell.Offset(1, 0).Select

  Next nm

End Sub
Bugs
  • 4,491
  • 9
  • 32
  • 41
ravi
  • 1
  • 1