VBA macro to copy hyperlink value to another worksheet

Hope this makes some sense.

I have a workbook where sheet 1 is a menu page where I have a list of items in cells G4:G10.
I have created hyperlinks to these cells (G4:G10) in A4:A10.

What I’m trying to do is when one of the hyperlinks is selected, the corresponding value in (G4:G10) is copied and pasted into cell B2 (always this cell) in sheet 2.
This value in sheet 2 B2 is then used for a vlookup in sheet 2.
Also sheet 2 B2 now becomes the active sheet.

regards
JF

Try this

Please Login or Register  to view this content.

Place this into Sheet1 (I assume this is the sheet where the hyperlinks are)
If in future please upload a sample workbook with no sensitive data(dummy data) showing how the workbook is constructed and showing the desired outcome as this makes supplying an answer much easier.
or

Please Login or Register  to view this content.