Need to create a Macro which convert the values in a column to Hyperlink.

Need to create a Macro which convert the values in a column to Hyperlink.


When I am running the recorded macro it is always assigning the same link to all the hyperlinks.

I want when I click on 'A' it should open Sheet with the name specified in the column 'D'.
ex.
Click on A should open sheet 'AA'
Click on B should open sheet 'BB' and so on.

Please refer to the attached sheet.Book2.xlsm

You can put this formula in A2:

=HYPERLINK("#"&D2&"!A1",D2)

and copy it down. Clicking on the cell will take you to cell A1 of the appropriate sheet.

Hope this helps.

Pete

If we click the link by the use of =HYPERLINK("#"&D2&"!A1",D2) formula "Reference text is not valid" error message displayed..

Thanks,
Deepan

I've attached a copy of your file in which I have done this for you.

Hope this helps.

Pete

Hi Pete,

When I tried to use the same formula for other values I am getting error message saying "Reference not valid."

Can you please check the attached sheet.ef50_hyperlinks.xlsm


Thanks

Because you have hyphens in the sheet names, you need to put apostophes within the formula. Amend the formula in A2 to this:

=HYPERLINK("#'"&D2&"'!A1",D2)

and then copy down.

Hope this helps.

Pete

Yes, It works. Thanks a lot

Okay, glad to hear that. If you are happy that your question has been answered, please mark the thread as Solved.

Pete