Search and Update Relative Hyperlinks -When Sheet is Duplicated

hi folks,

I thought this may be a simple find and replace but i was wrong.

Sheet1

January in cell A1 hyperlinks to Cell AX1

It works perfectly,

Duplicate Sheet 1


The hyperlinks point back to original Sheet1

file:///C:\Users\Desktop\Test.xlsm - Sheet1!AX1

This is where the problem is

What do i have to do to search and replace all these hyperlinks so they point within the new sheet ie they are relative

January in Sheet 2 Cell should be

file:///C:\Users\Desktop\Test.xlsm -Sheet2!AX1

instead of

file:///C:\Users\Desktop\Test.xlsm -Sheet1!AX1

I found this macro but it didnt work
http://stackoverflow.com/questions/2...nk-mass-update

how can i fix this
cheers
sl