savecopyas workbook - hyperlinks are changing to the new workbook name - erroneously

Okay,
wb1 is a template file (or a home workbook with multiple functions).
wb2 is a savecopyas wb that is made via macro within wb1. It is a slimmed down version made from a template within wb1
wb3 is a data file only.

Hyperlinks within wb1 are set to wb3 at it's address (fixed).
When wb2 is created via macro, the hyperlinks change address related to where wb2 is saved. I want them to remain fixed with where wb3 is.

the correct link is as follows (as taken from wb1):
=IF(ISNA(HYPERLINK(VLOOKUP(S44,'K:\swi\[SWIDATA.xlsm]Links'!$A$2:$B$9990,2,FALSE),Q44)),"",HYPERLINK(VLOOKUP(S44,'K:\swi\[SWIDATA.xlsm]Links'!$A$2:$B$9990,2,FALSE),Q44))

The incorrect link as created when the new workbook is made is as follows:
=IF(ISNA(HYPERLINK(VLOOKUP(S44,'K:\swi\Claim File Master\Claim Files\464004\[SWIDATA.xlsm]Links'!$A$2:$B$9990,2,FALSE),Q44)),"",HYPERLINK(VLOOKUP(S44,'K:\StillwaterInsurance\Claim File Master\Claim Files\464004\[SWIDATA.xlsm]Links'!$A$2:$B$9990,2,FALSE),Q44))

Claim File Master\Claim Files\464004 is clearly added because that is where the new wb2 is located. How do I stop this?

NOTE: i am open to suggestions (either vba when i create the copy or Excel in changing the template), thus the reason this has been posted in the VBA forum. I was unable to find a solution to this issue through days of research.

Thanks,

Darren

Originally Posted by :) Sixthsense :)

What about updating the formula via code while creating the tomb gin.workbook?

Absolutely not opposed to that. However, never been there with code. Wouldn't even know where to begin. Currently, excel handles my hyperlinks. I have already accepted the fact that I need to run code (it will reside in my savecopyas code) to handle activating each hyperlink.....so its just a small addition to the code. Can you point me in the right direction based on what is in the cells currently as indicated above?

Originally Posted by :) Sixthsense :)

Just change the A1:A5 to your desired range in the below code

Please Login or Register  to view this content.

Yeah.....figures It would be that easy. I will apply it tomorrow. Never used vba to enter formula before. Smooth. Thanks sense. I consider his thread solved and a * for you. Appreciate it.

Originally Posted by :) Sixthsense :)

Glad it helps you and thanks for the feedback

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hey Sense,

Please Login or Register  to view this content.

getting an error on this line. I trimmed it down to one line as indicated in my code but getting runtime error 1004. any thoughts?

I see. Oops. Thx again. Works like a charm. Double quotes inside quotes. In got sloppy.

D