Macro / VBA Hyperlink Cell reference

[Jamesb93]

Hi Everyone,

I was just wondering if anyone is able to provide me with a macro, which would be able to convert a specific cell text in to a hyperlink which goes straight to the sheet it was typed & referenced to.

For example, Cell A2 description is "C27" which links to a seperate sheet called C27. If I were doing this manually, I would have to click on the cell A2, click edit hyperlink > place in this document > Cell reference > C27. I am just looking for a way to save doing these steps.

Thanks a lot for the help!

[Eastw00d]

If you would use your macrorecorder and do all the steps one time and close the recorder, you have your macro.
You only need to adapt this to your other needs i.e. worksheets
in the mean time I give you this (also recorded):

Please Login or Register  to view this content.

Cheers
Erwin

[Jamesb93]

Thanks for the reply Erwin!

Sorry I'm a bit useless at this! The only problem with me recording the macro is that it always references the C27 tab and I need it to basically hyperlink to whatever worksheet is in the description box on the specific sheet I'm on, so for example say I have in other cells A25 i want that to hyperlink to worksheet A25 but with me recording the macro, that A25 description /macro I recorded earlier will go to C27 worksheet.

Am I missing something obvious here?

Thanks again

[Eastw00d]

OK, basically this recording says everything you need to know:
activesheet, i.e. the sheet where you want to have the link in, specifically in the cell Anchor:=Selection.
Then you have the address whereto the hyperlink points, in above case: Sheet2 cell A1, you can give this any address in the workbook, so you can change this in "A25!A1" provided worksheet A25 exists. And the texttodisplay needs no further commentary.

Can you work with this?
Cheers
Erwin

[Jamesb93]

Hmm, but say I have 20 worksheets and one worksheet could have references to 10 different sheets (Eg A2,B3,C7,D10 descriptions in different cells (to be hyperlinked) all are in worksheet A1, the macro wouldn't work for these very effectively would it?

Thanks again Erwin,
James

[Eastw00d]

In fact no, because you have to set up a hyperlink only once, and it will stay as long as you don't empty the cell.
for every hyperlink you must set up a reference like above, in different cells, for example:

Please Login or Register  to view this content.

But it comes very handy if your sheets will be deleted and you have to set up everything again.

Cheers
Erwin

[Eastw00d]

Having this said, I just found a rather old script that I used in the past, which might be interesting for you:

Please Login or Register  to view this content.

Cheers
Erwin

[Jamesb93]

Sorry for the late reply Eastwood, I've only just got round to testing this out! I tried the activesheet macro but I couldn't get it to work as I intended (which is basically if the word in the cell ="C1", then reference to sheet C1 as that macro / anchor appears to be only on the cell not what is in the cell. It's basically just a reference on anywhere in the particular sheet which is based on the word itself, i.e link to C12 sheet if word = C12 but that word could be in cell F20 (but only to hyperlink based on my selection not the whole page).

I also have no idea how to use the macro at the end although it does look rather interesting!

Cheers for the help though anyway!