Best way to Break Links?

[crburke92]

Hey all,

I'm more wondering the best method (or in your opinion, the best way) to do this as opposed to a how to.

I have made a pretty sizeable database (lets call it WB1) and I can export specific info out to other workbooks (Lets say WB2). I have ~45 different templates built in WB1, and depending on a certain row value, it copies a specific template and moves it to a designated file.

All templates in WB1 have XLookup formulas to find all info I need from the WB1 database, which works fine. Once I move the sheet from WB1 to WB2, as expected it creates a reference back to WB1.

Would a good option be to Break Links in WB2 so I'm just left with the value? I know this is possible to do in VBA and I could just tuck it in at the end of my macro. Is there a way to build my XLookup function with INDIRECT so that it does not pull the WB1 reference, and I could just have the info I need in WB1 copy over to a sheet in WB2 that the XLookup/Indirect formula references?

 

[Alex Blakenburg]

It will probably depend on how complicated your workbook is in terms of relationships between worksheets and external sources.
1) If you want to bring across a template and don't need the link by all means just bring it over as values.
2) If you need Xlookups to other sheets to still be active then copy over your template sheet and then copy over the sheets with the lookup tables as using sheet copy.
If you have tables in them you will need to do the sheets 1 by 1 if there are no tables you can do all the sheets at once.
3) Then you can either:
3a) Use Find & Replace to remove the filename part of the formula
or
3b) try something like this:

Sub ChgLinks()
    ActiveWorkbook.ChangeLink Name:=ThisWorkbook.Name, NewName:=ActiveWorkbook.Name _
        , Type:=xlExcelLinks
End Sub

 

[crburke92]

Workbook isn’t overly complicated as I’ve made it as user friendly as possible for others to come in and use. This whole project has been so we don’t have to fill out redundant information every year for the same equipment. This is the last thing I had to figure out before I can start building to its entirety.

I tried a few times to get the links to break via VBA, no errors but also isn’t breaking the links. I can break them manually fine though. That being said I went the route of copying and pasting special values over top top. That seems to be working fine. I really just wasn’t sure how many options there were in excel for doing this, or what would be considered the best way. Thanks for the reply!

 

[Alex Blakenburg]

No problem. Sorry I couldn't give you anything more definitive. There are some additional considerations if you are using Power Query.
Let us know if you are still trying to do something in VBA and it is not working.