Duplicate a spreadsheet to another workbook with modifying its formulas or the charts datasources

[XavierL]

Hey all,

I am trying to duplicate a spreadsheet from a workbook to another, withoutchanging the formulas or the datasources of the charts it contains. The trickis that the worksheet I want to duplicate has (tons of) formulas and graphsthat use data from other worksheets. If I use the duplicate function the formulasand the charts will source its data from the original workbook



(Simplified) example:



I have a workbook A with two sheets:

• X with cell C1 = [Y]D2
  
• Y

I want to duplicate X in the workbook B so that X’ cell C1= [Y]D2, not = [workbook A][Y]D2.

Whether I do it from the excel interface or by using thevba copy/paste functions, the new worksheet will source its data from theprevious workbook.

For the formulas, I can use the ‘replace all’ function toremove all the [workbook A] the sheetcontains (dirty but it does the work) but I can’t find a way do it for the datasources of the charts.

Any idea ?

 

[JLGWhiz]

You can copy the sheets and then on the Data tab of the Ribbon click 'Edit Links' and remove the links. Or you can do a SaveAs to a new name for the workbook (The old one will still be there) and then delete any data not generated by formulas and, if needed, delete sheets that you don't want. Both methods leave sheet with formulas intact without the links to the original workbook. But if you had external references in the old workbook, ie. references to ranges in otherworkbooks, as part of your formulas, you might lose them with the removal of links.