Edit Hyperlink To Work In New Location

I have a workbook that links to other workbooks, in short people add data in the "other" workbook and the new workbook, we'll call "over see" automatically updates to show this data as it is add. The problem I have at the moment is there are hyperlinks in column H in the "other" workbooks that I need to also automatically transfer/update into the "over see" workbook. When I copy pasted or linked the workbook by having each cell equal the corresponding cell in the "other" workbook(s), the hyperlink lost the first half of its code. When I tried to creat a macro to fix this, code below, it only works for the first hyperlink, all links are different, but with my current macro, they all become the same.

Macro attempt 1: [Code] ....