Formula changes on paste

[bedalhome]

I have dozens of identical workbooks. I want to add a worksheet to all of them that is a summary of the data in the workbook. When I copy the formulas from my example workbook into the other workbooks, the pasted formulas refer back to the example workbook instead of the workbook I pasted the formulats into.

I copy ='SCI Material Batch Tracker'!B25

but it pastes ='[MMS5039-090623A-00-R.xls]SCI Material Batch Tracker'!B25

How do I paste and break the link?

 

[AnalyticsGuy7]

After you paste the formula you can update the workbook that it is supposed to refer to.

In Excel 2007, you go to the Data tab, and then go to "Edit links" under the Connections tab. You then select your current workbook and make the switch.

In Excel 2003, you go to Edit -> Links, and do the same.

Hope that helps!

 

[bedalhome]

Thanks AG7. That does help me with my macro, since I recorded the steps you gave.

I am surprised there is not an option to break links when pasting.

Bryan

 

[AnalyticsGuy7]

Well, you should note that instead of copying the CELL, you could copy the FORMULA (up in the formula bar), and then it should copy just fine. Also note that updating the link will update ALL links that used the old workbook as a reference.

 

[bedalhome]

Good point. Actually I am copying about 20 cells to the new workbook. Breaking the link to all of them is what I need. Thanks again.