Moving data from one sheet to another via a hyperlink

Question

I've searched the existing questions and found similar questions and answers but none that meet my requirements.

I have a spreadsheet where I wish to be able to click on links in one spreadsheet to take you to the second, placing the value in the cell clicked into a field in the second.

For example, the first sheet in column A will contain the units IT, Finance, and Networks

Click on any of these fields should take the user to the second sheet called "Sub Unit" placing the value of the cell in A1 where I can then write if statements to populate the sub unit list, for example clicking on IT would put IT in cell A1 and then via if statements in A3-A5 would make those as

Security, Support, Development

Then I would reuse similar VB code so that if someone clicks on a sub unit name, it takes them to a third sheet that will show the details for the sub unit.

Hope that makes sense,

I'm very new to VBA a so please indicate where I should place any code (e.g. in the tab, right click, view code, or in some different place)

Answer 1

At Sheet 1 press ALT+F11 to get the VB editor. Copy & Paste this code as standard module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Range " & Target.Address & " was double clicked"
 On Error Resume Next
    Dim xRg As Range
    Set xRg = Application.Selection
    Range("A80").Copy Sheet2.Range("A80")
    Application.Goto Worksheets("Sheet2").Range("A80"), True
End Sub

How it works:

• Write some data in Cell A80 & double click the cell.
• You get the Message box to confirm which cell has been clicked.
• Press OK button.
• Excel will Copy & Paste the content of A80 & place the cell pointer to Cell A80 of Sheet 2.

N.B.

• Adjust the cell references & Sheet name in code as needed.
• You may refer Range of cells to Copy & Paste.
• To do so edit this line,,
• Range("A80:A85").Copy Sheet2.Range("A80")