Extend a VBA macro with a hyperlinks table

[excelactuary]

Hello!

I want to add following functionality to a vba macro. The program collects data from different sheets and writes it into a single sheet. The layout is like this:

Please Login or Register  to view this content.

In total, I have 12 data blocks with titles. There is always a blank line between the data blocks. Now, I want to add hyperlinks to the title cells A2, A18 etc. These hyperlinks should be in the cells N10, N11, N12 and so on. Also, N10 should contain the text information in A2.
So: if A2 contains the text "Title1", I would want to have a hyperlink in N10 called "Title 1" and jump to A2 when clicking N10.

It would be great, if the macro would be handle a random number of data blocks, so that I wouldn't have to tell beforehand how many blocks I have.

Do you have any ideas how to do this? Thank you very much!

[sintek]

Hi excelactuary

Any chance of a sample workbook depicting your requirement.

Edit: Not to worry, see if this is what you require:

Please Login or Register  to view this content.

[excelactuary]

Hello sintek!

Thank you so much, your code does exactly what I wanted. In the future, I will post a workbook sample.

One more question: if I want to add custom text in the cell N9, what would I add to the code?

Edit:

Got it:

Please Login or Register  to view this content.

[sintek]

if I want to add custom text in the cell N9

...Don't you mean custom text in N10, N11 etc
If so change this line of code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

or do you just want to add a heading in N9.

[excelactuary]

I just want a heading in N9.

[sintek]

add this line

Please Login or Register  to view this content.

under

Please Login or Register  to view this content.

Glad I could help. thanks for rep points.