VBA - copying a cell value from one sheet to another using a hyperlink

[manc]

Good morning,

I am looking for help with achieving the following using VBA:

cell DM20 - hyperlink to take user to 'Sheet2'!
cell B3 - value to copy

What i want to happen is when a user clicks on text in DM20, it takes them to 'Sheet2'! and pastes the value from 'Sheet1'!B3 into 'Sheet2'!B10

I've looked up a few examples but they all seem to be associated with a CommandButton, which i don't want.

Any help greatly appreciated.
Best regards
manc

 

[vcoolio]

Hello Manc,

Perhaps the following Worksheet_SelectionChange event will do the task for you:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("DM20")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Sheet2.[B10].Value = Sheet1.[B3].Value
Sheet2.Select

End Sub

It assumes that cell DM20 is in Sheet1 also.

To implement the code:-

- Right click on the Sheet1 tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

Every time that cell DM20 is clicked on, the code will execute.

I hope that this helps.

Cheerio,
vcoolio.

 

[manc]

Dear vcoolio,

Thank-you very much for your time and suggestion.
I haven't tried your code yet, but how could your example be amended slightly so that DM20 isn't constant?
Everything else is constant but DM20 isn't. For example it will also be DM57, DM94, DM131 etc (increments of 37).

Best regards
manc

 

[vcoolio]

Hello Manc,

You could amend this line:-

If Intersect(Target, Range("DM20")) Is Nothing Then Exit Sub

to

If Intersect(Target, Columns("DM:DM")) Is Nothing Then Exit Sub

Cheerio,
vcoolio.

 

[manc]

Sorry vcoolio,

I'm not explaining myself very well and i got my constants the wrong way round.

DM20 is correct in the first instance.
Then it becomes DM57, then DM94, DM131 (increments of 37 each time).
In DM21 will be a hyperlink to another worksheet, in DM22 will be a hyperlink to another worksheet, so can't use your second example of "If Intersect(Target, Columns("DM:DM")) Is Nothing Then Exit Sub"

The same rule applies to cell B3, First instance it is B3, then B40, then B77 (again increments of 37).

Hope this makes sense to you.

Best regards
manc

 

[vcoolio]

Hello Manc,

Sorry for the late reply. I've been away and stayed away from computers also! I'll get back to you tomorrow.
In the meantime, read up on the Mod function.

Cheerio,
vcoolio.

 

[vcoolio]

Hello Manc,

See if the following works for you:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim TR As Long
    TR = Target.Row
If Target.Column = 117 Or TR Mod 37 = 1 Then
Cancel = True
   Target.Offset(-17, -115).Copy Sheet2.[B10]
   End If
   
End Sub

Cheerio,
vcoolio.

 

[Lobo200]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Column = 117 And (ActiveCell.Row - 20) / 37 = Round((ActiveCell.Row - 20) / 37, 0) Then
  
     Worksheets(2).Select
     Worksheets(2).Range("b10") = Worksheets(1).Range("b3")


End If




End Sub