Replace links after moving worksheets from one workbook to another.

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
810
Office Version
  1. 365
Platform
  1. Windows
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..

VBA Code:
replacestring = "[" & newbook.Name & "]"

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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top