Copy/Store cell data to be use post Hyperlink?

[rhelm]

Hi,
I've created a helpful Hyperlink+VLookUp formula that could really benefit from a small addition. But I just can't seem to figure out a solution.

Here's my formula:
=HYPERLINK(VLOOKUP(A1,Tab2!$A$1:$B$52,2,1),"Name")

Basically, it returns a webpage stored another sheet that matches a referenced cell.

What I think would be a really helpful addition is if I could also COPY another cells data at the same time so that once that web page loads up, it's stored in my memory and thereby available to be pasted as needed. Something along the lines of: =HYPERLINK(VLOOKUP(A1,Tab2!$A$1:$B$52,2,1),"Name")+Copy that text in cell A2 so I can use in that there website!

Any thoughts on how to add something like this to my current formula?

Thank you!!
rhelm

 

[cooper645]

Re: How to Copy/Store cell data to be use post Hyperlink?

Is this just based in one single cell? If so you may be able to get around it with a dropdown to select the site to go to and then use a command button or shape which then runs a macro to retrieve the site address from your table and also copies the info to clipboard for use when you get to the site.
I don't believe there is a way to accomplish this in a formula though.

I have set something like this up at work, it basically directs the user to a site after they select various pieces of info they want to search for from a dropdown, and then they just paste it in with Ctrl+V when the site loads.

Let me know if this is something you would be interested in or if its a bit more complex than that.

 

[rhelm]

Re: How to Copy/Store cell data to be use post Hyperlink?

Hi cooper645,
Thanks for the response! Yes, a single cell is all i need to capture (store to the clipboard). But I'm not sure about using a drop down (there are MANY sites in my array). What I like about this hyperlink/vlookup approach is that is that it's one stop shopping: a single mouse click to look up the variable, match it it's corresponding website, and then loads that external page. I have thousands of these types of checks so 'single click' solutions are a huge plus!

Now I just need to find a way to append a second formula to my existing hyperlink that will simply 'copy' a cells data (a two part problem: 1. I'm not sure what the syntax is for joining 2 formulas in a cell. And 2. how to copy via a formula) I would think that wouldn't be too challenging but it's testing my novice abilities for sure!

 

[cooper645]

Re: How to Copy/Store cell data to be use post Hyperlink?

Without knowing all the specifics, I have assigned the below code to a Button on Sheet1

• I assume that Sheet1 cell A1 contains the lookup value
• It looks up in a sheet named Tab2 range A1:B52
• The lookup value lies down column A in Tab2
• The website address/file address lies in column B in Tab2
• I have placed the search term in Sheet1 C1 but this can be changed if the search term is stored elsewhere. (VLookup possible also)

Let me know if this is a workable solution.

To adapt it further, if this is just an example I require more info

1. Sheet Names in use
2. Complete ranges
3. Which info lies in which column of the ranges
4. How you determine the website/file to lookup in that range {ie Dropdown/user entered term}

[COLOR=#0000ff]Option Explicit[/COLOR]

[COLOR=#0000ff]Sub[/COLOR] MrExcel1091486()
[COLOR=#008000]'https://www.mrexcel.com/forum/excel-questions/1091486-copy-store-cell-data-use-post-hyperlink.html

'This macro simply looks up the value in Cell A1 on Sheet1 in the sheet named Tab2
'and returns the hyperlink to the right of the location.  It then copies the string in cell C1 on Sheet1
'which could in turn be changed to a VLookup if the search strings are already stored somewhere
'Then goes to the website where the end user can paste the value with Ctrl+V or right click paste
[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] Hyper [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR], LookVal [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]


[COLOR=#008000]'LookVal could be adapted if need be, depending on requirement[/COLOR]
LookVal = Sheets("Sheet1").Range("A1").Value

Hyper = WorksheetFunction.VLookup(LookVal, Sheets("Tab2").Range("A1:B52"), 2, [COLOR=#0000ff]False[/COLOR])

[COLOR=#008000]'Copy the search term to clipboard[/COLOR]
ActiveSheet.Range("C1").Copy [COLOR=#008000]'This is the location of the search term but culd easily be another VLookup
[/COLOR]
ActiveWorkbook.FollowHyperlink Address:=Hyper [COLOR=#008000]'Go to the webpage with the info on clipboard
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[rhelm]

Re: How to Copy/Store cell data to be use post Hyperlink?

I think that might work. I'll give it a test run here shortly.

Thank you, cooper645!

 

[rhelm]

Re: How to Copy/Store cell data to be use post Hyperlink?

Worked! Huge thanks, cooper645!

 

[cooper645]

Re: How to Copy/Store cell data to be use post Hyperlink?

No problem, if you need any assistance adapting it just let us know.