Excel VBA需求：点击超链接跳转并填充目标单元格内容

Got it, let's work through this exactly how you need it. You want every name in Column E of your first sheet to act as a hyperlink—clicking it should jump to Sheet2!D13 and drop the clicked name into that target cell. Here's the step-by-step fix using VBA, since regular hyperlinks can't handle the "populate cell" part on their own:

Step 1: Automatically Add Hyperlinks to Column E #

First, let's add hyperlinks to all your names in Sheet1's Column E. If you have a ton of names, this macro will save you time instead of doing it manually:

Sub AddHyperlinksToColumnE()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
    ' Loop through each non-empty cell in Column E
    For Each cell In ws.Range("E1:E" & lastRow)
        If cell.Value <> "" Then
            ws.Hyperlinks.Add _
                Anchor:=cell, _
                Address:="", _
                SubAddress:="Sheet2!D13", _
                TextToDisplay:=cell.Value
        End If
    Next cell
End Sub

To run this: Open the VBA editor (press Alt + F11), insert a new module, paste the code, and hit run.

Step 2: Add the Click Event to Populate the Target Cell #

Now we need to capture when someone clicks one of those hyperlinks and copy the name to Sheet2!D13. Here's how:

1. Right-click the Sheet1 tab at the bottom of Excel.
2. Select View Code from the menu.
3. Paste this code into the blank code window that opens:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    ' Only act if the hyperlink points to Sheet2!D13
    If Target.SubAddress = "Sheet2!D13" Then
        ' Copy the clicked name to the target cell
        ThisWorkbook.Worksheets("Sheet2").Range("D13").Value = Target.Range.Value
    End If
End Sub

How This Works #

• The first macro adds hyperlinks that all jump directly to Sheet2!D13, using the existing name in each cell as the display text.
• The worksheet event triggers every time a hyperlink in Sheet1 is clicked. It checks if the hyperlink is targeting Sheet2!D13, then takes the value of the clicked cell and drops it into that target cell—right after jumping there.

Quick Notes #

• Double-check your sheet names! If your target sheet isn't named "Sheet2", update the code to match its actual name (e.g., replace "Sheet2" with "CustomerList").
• If you add new names to Column E later, just re-run the AddHyperlinksToColumnE macro to add hyperlinks to the new cells.

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