Hyperlinks security warning

[Peter3280]

Hello everyone,
I am relatively new at macros but I have been able to build a macro to create a series of monthly cost reports to be distributed to different managers.
The workbook consists of two tabs a distribution tab (who gets what report) and the report itself which is linked to our BI system.
The distribution tab has 30 managers and part of the layout is setup as outlined below.
<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 565pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=753><TBODY><TR style="HEIGHT: 47.25pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 47.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=263>Business Unit<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 47.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=139>

General Manager – Operations<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 47.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=117>

Site Production Manager<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 47.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=117>

Production Supervisor – Powder<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 47.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=117></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=263>

0<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=139>

1<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=117>

2<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=117>

3<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" width=117></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>1515 - Supplier Intake<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>1520 - External Intake<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>1532 - Site Services<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>1547 - Distribution Milk<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>1570 - Milk Trading Sales<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>4510 - Field Service<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>4511 – Cartage<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap>

X<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 9"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>4512 - Tanker Washing<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap>

X<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 10"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>4513 - Milk Supply Handling – SA<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap>

X<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 11"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>4514 - Supplier Testing<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 12"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>4519 - Operations Management<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap>

X<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 13; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 197pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=263 noWrap>4521 - Standardisation and Separation<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=139 noWrap>

X<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap>

X<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 88pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0cm" vAlign=bottom width=117 noWrap></TD></TR></TBODY></TABLE>
<o> </o>
Each of the manager’s titles is a hyperlink to the macro which will create the desired reports and saves the file as the manager’s title with year and month (my code is below for reference). There is also a macro to create all 30 manager’s reports if required.
The macro executes with no problems on my ‘C’ drive in “C:\Users\Peterk\Documents\Cost Report Testing\Jul”. However, when I move/copy the file to a network drive “K:\Financial Accounting\FY 1112\EOM - 1107\TMI Cost Centre Reports” I get a “Microsoft Office Excel Security Notice. Microsoft Office has identified a potential security concern” and the user has to select Yes or NO to continue. The hyperlink is ‘pointing’ to the old location (C:\Users\Peterk\Documents\Cost Report Testing\Jul) when I hover over the link. The only way I can find to correct the issue is to manually open each hyperlinked cell and point it to the new location (‘K:\Financial Accounting\FY 1112\EOM - 1107\TMI Cost Centre Reports’) and the warning goes away. I have tried the Excel hyperlink function without success. This seems to be looking for a file not the macro within my file.
Is there any way to automatically update the hyperlink ‘path’ to the current location?
Any suggestions appreciated.
Regards
Peter
<o> </o>
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)<o></o>
Dim ForwardColumns As Integer<o></o>
Dim BackColumns As Integer<o></o>
Dim MyTabLabel As String<o></o>
Dim MyFileName As String<o></o>
Dim MyMonth As String<o></o>
Dim MyYear As String<o></o>
Dim MyFileSaveName As String<o></o>
Dim SheetCounter As Integer<o></o>
Dim Recipient As String<o></o>
'Load Distribution into file name<o></o>
MyFileName = ActiveCell.Value<o></o>
'Load File recipeint's email address into Recipient<o></o>
ActiveCell.Offset(-1, 0).Select<o></o>
Recipient = ActiveCell.Value<o></o>
ActiveCell.Offset(2, 0).Select<o></o>
'Load the number of columns from column a into the column counter<o></o>
'ActiveCell.Offset(1, 0).Select<o></o>
ForwardColumns = ActiveCell.Value<o></o>
BackColumns = ActiveCell.Value * -1<o></o>
'Select the 1st business unit in the business unit list<o></o>
ActiveCell.Offset(1, BackColumns).Select<o></o>
'Loop while the business unit is not blank<o></o>
'Add a new work book and save the file<o></o>
Windows("CostReport.xlsm").Activate<o></o>
Worksheets("Report").Activate<o></o>
Sheets("Report").Range("C11").Select<o></o>
MyMonth = ActiveCell.Value<o></o>
Sheets("Report").Range("C10").Select<o></o>
MyYear = ActiveCell.Value<o></o>
Workbooks.Add<o></o>
MyFileSaveName = MyFileName & "-" & MyMonth & "-FY" & MyYear<o></o>
Application.DisplayAlerts = False<o></o>
ActiveWorkbook.SaveAs Filename:= _<o></o>
MyFileSaveName _<o></o>
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False<o></o>
Application.DisplayAlerts = True<o></o>
Windows("CostReport.xlsm").Activate<o></o>
Worksheets("Distribution").Activate<o></o>
<o></o>
Do While ActiveCell.Value <> 0<o></o>
'Select the column with the business units selected for the managers distribution<o></o>
ActiveCell.Offset(0, ForwardColumns).Select<o></o>
'If the business unit is selected to be distributed<o></o>
If ActiveCell.Value = "X" Then<o></o>
'Get the Cost Centre Information<o></o>
ActiveCell.Offset(0, BackColumns).Select<o></o>
'Load Cost Centre Info into the Tab Name variable<o></o>
MyTabLabel = Left(ActiveCell.Value, 30)<o></o>
ActiveCell.Copy<o></o>
'Load the cost centre information into the cost report<o></o>
Windows("CostReport.xlsm").Activate<o></o>
Worksheets("Report").Activate<o></o>
Sheets("Report").Range("C12").Select<o></o>
ActiveSheet.Paste<o></o>
'Load Month Information into the Month variable<o></o>
Sheets("Report").Range("C11").Select<o></o>
MyMonth = ActiveCell.Value<o></o>
'Load Year Information into the Year variable<o></o>
Sheets("Report").Range("C10").Select<o></o>
MyYear = ActiveCell.Value<o></o>
'Show all rows in the cost report with values<o></o>
Application.Run "'CostReport.xlsm'!Hiderows"<o></o>
'Recalculate values for the new cost centre<o></o>
Application.Run "'CostReport.xlsm'!Recalculate"<o></o>
'Hide rows with Zero values in the cost report<o></o>
Application.Run "'CostReport.xlsm'!Hiderows"<o></o>
'If this is the first business unit to be distributed to the manager save the workbook<o></o>
If SheetCounter = 0 Then<o></o>
<o></o>
'Select the Cost Report and copy to the new file removing any formulas<o></o>
Windows("CostReport.xlsm").Activate<o></o>
Sheets("Report").Select<o></o>
Selection.SpecialCells(xlCellTypeVisible).Select<o></o>
Sheets("Report").Copy Before:=Workbooks(MyFileSaveName). _<o></o>
Sheets(1)<o></o>
Sheets("Report").Cells.Select<o></o>
Selection.Copy<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
Sheets("Report").Name = MyTabLabel<o></o>
Windows("CostReport.xlsm").Activate<o></o>
Worksheets("Distribution").Activate<o></o>
Else<o></o>
Windows("CostReport.xlsm").Activate<o></o>
Sheets("Report").Select<o></o>
Sheets("Report").Copy Before:=Workbooks(MyFileSaveName). _<o></o>
Sheets(1)<o></o>
Sheets("Report").Cells.Select<o></o>
Selection.Copy<o></o>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
Sheets("Report").Name = Left(MyTabLabel, 31)<o></o>
Windows("CostReport.xlsm").Activate<o></o>
Worksheets("Distribution").Activate<o></o>
End If<o></o>
SheetCounter = SheetCounter + 1<o></o>
Else<o></o>
ActiveCell.Offset(0, BackColumns).Select<o></o>
<o> </o>
End If<o></o>
ActiveCell.Offset(1, 0).Select<o></o>
Loop<o></o>
Windows(MyFileSaveName).Activate<o></o>
Application.CutCopyMode = False<o></o>
Application.DisplayAlerts = False<o></o>
ActiveWorkbook.SaveAs Filename:=MyFileSaveName<o></o>
ActiveWorkbook.Close<o></o>
Application.DisplayAlerts = True<o></o>
Windows("CostReport.xlsm").Activate<o></o>
Sheets("Distribution").Range("B3").Select<o></o>
End Sub<o></o>

 

[Jerry Sullivan]

Hello Peter,

This thread shows an approach that might work for your scenario.
It involves having a cell that references the current folder of the workbook, and having the hyperlinks reference that cell for the path.

http://www.mrexcel.com/forum/showthread.php?t=520889

Let me know if you want any help adapting your VBA code to use that approach.

 

[Peter3280]

Thankyou for the help Jerry.

I now have the "path" being updated in the hyperlink using an Excel hyperlink formula but now the macro will not execute when I select it.
I am pretty sure it has something to do with the followhyperlink command.
The one I'm currently using is

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

I built the macro using the View Code icon on the developer ribbon.
(Not sure how else to explain that.)
Does anyone know if there a viable alternative?.

Regards
Peter

 

[Jerry Sullivan]

Peter,

You'll need to clarify that a bit. Did you try to use the same approach as that shown in the referenced thread?
That method does not require the use of macros or VBA event code.

Please explain how you have your Hyperlinks set up.

 

[Peter3280]

Thanks Jerry;
My hyperlink formula is;
=HYPERLINK(Filepath&"CostReport.xlsm","Cheese Plant Supervisor")
Where the Filepath is dynamic using the Excel formula you suggested;
=LEFT(CELL("filename"),SEARCH("[",CELL("filename"))-1)
And creates the filepath ‘C:\Users\Peterk\Documents\Cost Report Testing\Aug\’<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
When I click on the hyperlink constructed this way the “view code” (on the developer ribbon) macro will not execute.<o></o>
I am also using the Insert>Hyperlink dialogue box linking to the “CostReport.xslm” and using this method the “view code” macro does execute but face the path issue I set out to solve.<o></o>
I think there is a ‘compatibility’ issue with the hyperlink formula and the 1<SUP>st</SUP> line of my macro.<o></o>
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink).<o></o>
Regards<o></o>
Peter

 

[Jerry Sullivan]

Hi Peter,

That helps, but I'm not understanding why you are trying to View Code or use a FollowHyperlink event.

If you make the formula as you have, your user can click on the Hyperlink and it will open the other file...without the need for VBA.

Excel Workbook
	A
1	C:\TEST\
2
3	Cheese Plant Supervisor
4
5
Hyperlink

#VALUE!

</td></tr></table></td></tr></table>

Is there some other reason you are trying to incorporate VBA?

 

[Peter3280]

Thanks Jerry,

Solved it using the Insert>Hyperlink ribbon and linking to "a place in the document" rather than existing file or web page which I was previously doing.

So now I have my hyperlink "Cheese plant supervisor" in E2 pointing to cell reference E2 on the distribution tab of my CostReport workbook. When this is selected the macro executes and the file path changes when the file is moved.

Regards
Peter

 

[Jerry Sullivan]

Peter,

I responded too fast earlier and didn't remind myself of your Original Post. Sorry to send you off in that direction.

When I saw that proposed solution wasn't a good match, I started looking for alternatives.

The one you arrived at is on my list. The only downside is that the hyperlink itself no longer has any intelligence about the link address you ultimately want to follow. Instead you need to embed that into your VBA code.

Another option that might be a better approach would be to have a function that changes some or all of your hyperlinks addresses to reference the current folder. That function could be called by the Workbook_Open event to update your hyperlinks if the file is moved to another folder.

I'd be glad to help with the second option if you want to go that route- but it sounds like you have a working solution. Nice going!

 

[Peter3280]

Hi Jerry,

I would be interested in your alternative solution to the one I have particularly if it is more robust.

Regards
Peter

 

[Jerry Sullivan]

I would be interested in your alternative solution to the one I have particularly if it is more robust.

Here is my first pass at a function that will take all the File Hyperlinks in a Worksheet and change their Address Path's to the Path specified.

Function RePath_Hyperlinks(ws As Worksheet, strNewPath As String)
    Dim hypX As Hyperlink, strFilename As String
    For Each hypX In ws.Hyperlinks
        With hypX
            If .Address <> "" And _
                Not (UCase(.Address) Like "HTTP:*") Then
                strFilename = GetFileName(.Address)
               .Address = strNewPath & "\" & strFilename
            End If
        End With
    Next hypX
End Function

Function GetFileName(strFullPath As String) As String
    Dim strFind As String, strSep As String, iCount As Long
    If InStr(strFullPath, "/") Then
        strSep = "/"
    ElseIf InStr(strFullPath, "\") Then
        strSep = "\"
    Else
        GetFileName = strFullPath
        Exit Function
    End If
    
    Do Until Left(strFind, 1) = strSep
        iCount = iCount + 1
        strFind = Right(strFullPath, iCount)
        If iCount = Len(strFullPath) Then Exit Do
    Loop
    GetFileName = Right(strFullPath, Len(strFind) - 1)
End Function

I've tried to account for exceptions to File-Targeted Hyperlinks such as those with addresses to URL's, links within the same workbook, and use of the =HYPERLINK() formula. There are probably some scenarios I haven't considered, so as always - test this on a copy of your workbook first!

The calling Sub might look something like this, which could be called as needed or placed in the Workbook_Open event code.

Sub RepathToCurrentFolder()
    Dim strNewPath As String
    strNewPath = ActiveWorkbook.Path
    Call RePath_Hyperlinks(Sheets("Sheet1"), strNewPath)
End Sub

Hope this helps!