VBA for copy/paste when clicking hyperlink

Hello all,

My apologies for the repeat thread but I cant seem to work this one out and am hoping you can help.

I have attached a workbook that is similar to the one I am working on and I want something along the lines shown in my rather sad looking code to happen.

the code below (adapted from Daves) does not even pretent to work but I am hoping you can see what I mean to do (I know nothing of VBA). I have put code tags around it and hope they work?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Select Case Target.Address


Case "$A$8"


if a8<> ""


Range("sheet1!b4").copy
Range("sheet2!a9").paste


Application.CutCopyMode = False


else nothing


case "$A$9"


if a9 <> ""


range("sheet1!b5").copy
range("sheet2!a9").paste


application.cutcopymode = false


else nothing


end sub

I guess I just need to work out a couple of things with the code...

for example why range("x").copy works as code but range("y").paste does not.

what do you use to make the macro activate only if there is a hyperlink showing (so that if you get a mad-clicker user you dont wind up with blank cells being copied)

and finally whether the hyperlink will continue as usual once the macro has run.

Thankyou for your time with this

Justin

Thankyou royUK for taking a shot at it, I think however I must be explaining things completely wrong so I will try again with an updated sheet.

In the file I would like to be able to update the cell range named "outcell" which would update the range "tableone" with the appropriate hyperlinks.

Dave Hawley showed me a VBA code using "case" however this would put an output into the "outcell" wheneever someone clicked on the range specified in the case. The hyperlinks that appear in the cells may be entirely different depending upon the search text entered (if it is found).

I think that the code (using "case") would do the job perfectly if I could see how to make the output vary depending what appears in a second cell. ie rather than have
when a4 is selected the outcell value is changed to "a", I would like

when a4 is selected the value from b4 is copied to the outcell (and this only occurs if the hyperlink is active (ie if(a4 <> "", copy(value(b4), outcell)), "").

that is obviously not code but hell I dont know any code.

Thanks again for the replies so far, I am learning as always.

copy/paste with hyperlink click

this was the thread Dave.

Thankyou for your time.

Justin