通过VBA创建指向Stock Code系列工作表的超链接需求

Here's a straightforward VBA macro that will handle creating those hyperlinks exactly as you described:

Sub CreateStockCodeHyperlinks()
    Dim wsIndex As Worksheet
    Dim ws As Worksheet
    Dim lastMRow As Long
    Dim currentRow As Long
    Dim stockNumber As String
    
    ' Set reference to the Index Page worksheet
    Set wsIndex = ThisWorkbook.Worksheets("Index Page")
    
    ' Find the last used row in column M, then start from the next row
    lastMRow = wsIndex.Cells(wsIndex.Rows.Count, "M").End(xlUp).Row
    currentRow = lastMRow + 1
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet name starts with "Stock Code "
        If Left(ws.Name, 11) = "Stock Code " Then
            ' Extract the number part from the worksheet name
            stockNumber = Mid(ws.Name, 12)
            
            ' Create the hyperlink in column M
            wsIndex.Hyperlinks.Add _
                Anchor:=wsIndex.Cells(currentRow, "M"), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!A1", _
                TextToDisplay:=stockNumber
            
            ' Move to the next row for the next hyperlink
            currentRow = currentRow + 1
        End If
    Next ws
    
    MsgBox "Hyperlinks created successfully!", vbInformation
End Sub

Key Details & Explanations: #

• Targeting the Index Page: We first set a reference to the "Index Page" worksheet so we can easily work with it throughout the macro.
• Finding the Starting Row: The code uses End(xlUp) to find the last filled row in column M, then starts adding hyperlinks from the row immediately after that.
• Filtering Valid Worksheets: We check if a worksheet's name starts with "Stock Code " to make sure we only process the relevant sheets.
• Extracting the Stock Number: Using Mid(ws.Name, 12) grabs everything after the first 11 characters (which is "Stock Code ") — this gives us the numeric part to display.
• Creating Hyperlinks: The Hyperlinks.Add method links to cell A1 of the target worksheet (you can adjust the SubAddress if you want to link to a different cell). The TextToDisplay parameter sets the visible text to the extracted stock number.

How to Use: #

1. Open your workbook.
2. Press Alt + F11 to open the VBA Editor.
3. Insert a new module (Right-click your workbook in the Project Explorer > Insert > Module).
4. Paste the code above into the module.
5. Press F5 to run the macro, or assign it to a button on your worksheet for easier access.

内容的提问来源于stack exchange，提问作者arijitirf