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?

    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


  • I am not clear what you are asking for, but I have code to the CombBox Change Event that will add a hyperlink in A8 of Sheet1 to the respective ranges in Sheet3.

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!