Replace links after moving worksheets from one workbook to another.

[RockandGrohl]

Hello

I'm taking a workbook with many sheets in, and using VBA to drag copy these worksheets into a new template workbook. I'm then copying the formulas from a summary sheet into my new template workbook, and of course it references the sheets on the old workbook. Annoyingly, the formula is like this:

Sheet 1 Range B10 + Sheet 2 Range B10 + Sheet 3 Range B10 etc

What I'd like to do is a find and replace on the summary sheet in the template, after all the tabs have been copied over, and replace the reference to the sheets on the old workbook, with the sheets on the new workbook. Is this possible? ChatGPT seems to think a find and replace would do the trick but of course that's not the way it works in practice. Thanks.


EDIT: Ah nevermind, sorted it, had some stuff in the wrong place. In case anyone wants to know how..

replacestring = "[" & newbook.Name & "]"

ct.Range("B1:E38").Replace What:=replacestring, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

 

[number kruncher]

One way to do this is Save your new Workbook, then

use Data, Workbook Links, Change Source (from the 3 dots of your current Link) and choose your new workbook from the form menu

Hope this helps

nk

 

[RockandGrohl]

One way to do this is Save your new Workbook, then

use Data, Workbook Links, Change Source (from the 3 dots of your current Link) and choose your new workbook from the form menu

Hope this helps

nk

That's a good idea, unfortunately not applicable here because the user saves the workbook at the very end once they're happy with changes, and it's on a sharepoint site so the macro is useless at saving. Thanks though!