Making this simple macro 'relative'.

In my screenshot, row 23 contains abbreviated names for hyperlinks that I want to create in row 24. That set of cells (B24:J24) initially contains the text of full filenames, with path.

MakeMacroRelative-1.jpg

To save considerable manual effort (there are many sheets like this) I'm trying to write a macro which will convert that text to a hyperlink, and replace the original text with my abbreviated names from B23:J23.

I've recorded the following:

Please Login or Register  to view this content.

At present, of course, wherever I apply it, it just duplicates the specific col B result. I'm struggling with how to edit that to make it 'relative' - something I always have difficulty with.

So any help would be much appreciated please.

Hi Terry,

See if this works for you?

Please Login or Register  to view this content.

Many thanks Arkadi, that works just fine! Grateful for such a fast response.

Just one problem. As a VBA novice it took me a while to realise it would not work in the first place I inserted it, in Personal.xlsb. I've had to place it in this specific worksheet. Can you please advise how I can make it more general, working either from any workbook, or from one specific workbook called Walk Index.xlsm that will always be open when I want to work on Track Data.

Hmm... my macro would run just fine from a standard module... it does not need to be in a specific worksheet. However, if you want to generalise it, say to run from where it is stored, but affect the active workbook and sheet, you can change it to:
(I also forgot to change "ActiveSheet" to "ws" in my code...)

Please Login or Register  to view this content.

p.s. Thanks for the rep

Thanks Arkadi, I'll try that first thing tomorrow.

EDIT: Briliant, now works exactly as I wanted, thank you!