创建一个新的表格，然后在摘要表中超链接

Question

我已经开发了一个代码来复制表格并将其重命名为插入框中给出的值，然后在摘要表中复制相同的值，然后选择最后一个单元格和粘贴值但我想创建超链接，以便如果我单击该值，它将带我到该表。

我被困在适当的子贴上。

Private Sub CommandButton1_Click()
    Dim sName As String
    Dim oRng As Range
    sName = InputBox("New Shipment", "New AWB Number", "Enter the AWB Number")
    If sName <> "" Then
        ThisWorkbook.Sheets("Templete").Copy Before:=Sheets(3)  
        ActiveSheet.Name = sName
        MsgBox "New AWB Number Tracking Added"
    Else
        MsgBox "Failed"  
    End If
    Sheets("Summary").Select
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = sName
    'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'Sheet(3)'!A1"
End Sub

当我运行此问题时，我会遇到错误：

"参考不有效"

请帮助。

Answer 1

使用Select和Activate时，您总是可以遇到问题，但我怀疑您的主要问题是您的超链接子地址。您试图将其设置为'Sheet(3)'!A1"的地方无法识别它，因为它是工作表(VBA识别(而不是表名称。看看以下

Private Sub CommandButton1_Click()
    Dim sName As String
    Dim oRng As Range
    Dim nWs As Worksheet
    sName = InputBox("New Shipment", "New AWB Number", "Enter the AWB Number")
    If sName <> "" Then
        With ThisWorkbook
            .Sheets("Templete").Copy Before:=.Sheets(3)
            Set nWs = .Sheets(3)
        End With
        nWs.Name = sName
        With Sheets("Summary")
            With .Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
                .Value2 = sName
                .Parent.Hyperlinks.Add Anchor:=.Cells, Address:="", SubAddress:= _
                                        "'" & nWs.Name & "'!A1"
            End With
        End With
        MsgBox "New AWB Number Tracking Added"
    Else
        MsgBox "Failed"
    End If
End Sub

您可能还应将其添加到该检查中以针对用户输入的重复或无效名称进行测试。