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

XavierL

New Member
Joined
Feb 25, 2019
Messages
1
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 ?


 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,446
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,425
Messages
5,528,688
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top