VBA在工作簿中命名范围的超链接列表

Question

我正在尝试创建VBA代码，该代码在我的工作簿中识别命名范围，并为每个代码创建一个超链接。这些超链接需要能够复制到其他文档中。

我找到了两个例子 - 每个人都做了一半的工作，但是将它们结合在一起很难：

1(查找和列出范围：

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

2(创建所有工作表的超链接：

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

当我组合时，如下所示，我会收到一个错误/变量未设置的错误。

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

最终代码不需要像第一个示例一样产生范围的清单。

Answer 1

我认为您是按照下面的代码之类的东西，它将在" A"列中添加每个单元格(从第2行开始(，命名范围的名称，并链接单击Hyperlink。

代码

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

Answer 2

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