Changing Data Source - Is there an easier way?

[colinheslop1984]

I have multiple workbooks all identical but contain data for individual sites.

I have recently introduced a new worksheet which I created on one of the workbooks and I wanted to copy it and paste it in to a new blank worksheet for each sites own document but when I do this it also copies in the link for the originating workbook as part of the formula's set out, which I then have to go through and delete it for each cell which is very time consuming.

Surely there must be an alternative way that is more efficient? Can any one help me out?

 

[pgc01]

Hi

Try editing the links, change source to the current workbook.

 

[bobsan42]

several ways to do it - depending which works best for you:
1. open New Formulas sheet in Formula view -> copy -> paste in Notepad -> copy from Notepad > Paste in new excel sheet (not universal)
2. Copy->Paste-> Use Replace (Ctrl+H) to remove ext.ref. from formulas
3. Copy->Paste-> Go to Data Tab > Edit links ...
4. Open the two workbooks, select the area to transfer the formulas to, then in the immediate window of VBE editor (Alt+F11 then press Ctrl+G) type and press emter:

selection.formula=Workbooks("book1.xlsx").Worksheets("Sheet1").range("A1:D26").formula

You have to adapt the parts in the quotes to match your file/sheet/range - once set the same can be used for all other files.

 

[colinheslop1984]

Amazing, so simple! Replace worked a treat. Thanks you