How to select cell next to macro linked hyperlink?

Question

I have a hyperlink with a code linked to it in cell A1.

Cells B1->E1 have data in them

I want once the hyperlink is clicked, to cut cells B1->E1

I want to paste these in a different tab but I am familiar with that part of the code.

This is a big sheet so I don't want to cut cells B1->E1 specifically. I want to cut the cells next to the hyperlink whichever row is clicked.

What I am trying to do;

A                   B            C              D           E  
hyperlink          value        value          value      value      
hyperlink          value        value          value      value      
hyperlink          value        value          value      value      
hyperlink          value        value          value      value

Code I tried

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 
    Dim r As Range 
    Set r = Range(Target.SubAddress) 
    Range("B7:E7").Select 
    Selection.Cut 
    Sheets("Wash Bay").Select 
    Worksheets("Wash Bay").Range("B6").Activate 
    ActiveSheet.Paste 
End Sub

Welcome to SO. Please understand this is not a code writing service. Please post the code you have tried and if you ran into any specific errors. I suggest reading the following [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) and [How to Ask](https://stackoverflow.com/help/how-to-ask) — Zack E, Nov 04 '19 at 20:36
Hi sorry about that , I do have a code kinda but I didn't know I was supposed to post, thanks for letting me know. here is what I got ; Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim r As Range Set r = Range(Target.SubAddress) Range("B7:E7").Select Selection.Cut Sheets("Wash Bay").Select Worksheets("Wash Bay").Range("B6").Activate ActiveSheet.Paste End Sub — Keeyask, Nov 04 '19 at 20:47

score 1 · Answer 1 · answered Nov 04 '19 at 20:59

1

You can use:

r.Offset(0, 1).Resize(1, 4).Cut Sheets("Wash Bay").Range("B6")

Note you can use the Destination parameter for Cut to specifiy where to paste the cut cells. No need to select/activate.

answered Nov 04 '19 at 20:59

Tim Williams

154,628
8
97
125

score 1 · Accepted Answer · answered Nov 04 '19 at 21:00

1

You are (almost) getting a reference to the range you want (in r), then not using it.

And, you don't need any of that Select / Activate stuff anyway (see here for useful tips)

Your code could be simply (assuming sheet "Wash Bay" is in the same workbook as the code):

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim r As Range
    Set r = Range(Target.SubAddress).Offset(0, 1).Resize(1, 4)
    r.Cut ThisWorkbook.Worksheets("Wash Bay").Range("B6")
End Sub

answered Nov 04 '19 at 21:00

chris neilsen

52,446
10
84
123

cool thanks , that makes the code a little neater. if I have a follow up question on the pasting part of this do I need to start a new thread or can I just post the question here with you? – Keeyask Nov 04 '19 at 21:26
Generally ask a new Q if it can be asked as a good stand alone Q- you'll get more eyes on it. If it's a minor point, I don't mind addressing it here – chris neilsen Nov 04 '19 at 22:52
I actually got it figured out, thanks anyways though I appreciate the help! – Keeyask Nov 04 '19 at 23:38

How to select cell next to macro linked hyperlink?

2 Answers2