Copying sheet without changing references/formulas (2000 SP3)

Topic

New Reply

Hi Everyone!

Here’s the scenerio:

• WorkbookA has 3 worksheets: SheetA, SheetB and SheetC
• WorkbookB has 5 worksheets: SheetA, SheetB, SheetC, and two newly created sheets: NewSheetA and NewSheetB
• SheetA, SheetB and SheetC are identical in both workbooks
• NewSheetA and NewSheetB have all kinds of cells referencing cells on SheetA, SheetB and SheetC
  [/list]How can I copy NewSheetA and NewSheetB to WorkbookA without the references changing to include “=[WorkbookB.xls]…” all over the place?… I simply want the formulae and references to remain exactly the same, thereby referencing the cell in the current workbook… Does that make sense?…. Is this possible?? Seems as though it should be…

  Thanks in advance!

Reply | Quote

Replies

If you copy or move NewA and NewB from workbook B to workbook A you can then change the “links” in A (which will reference wb to reference wb A.

• Save both wb’s – particularly, save wbA with a new name – just in case it doesn’t work…
• Copy the sheets NewA and NewB from wbB to wbA the version with the new name
• Close wbB
• Save wbA, then open the menu item “Edit | Links this will open a dialogue box with all the links listed
• Click on “Change Source” on the right (if there is more than one source listed, make sure you have selected the reference to wbB)
• This opens a regular file list dialogue box – click on the name of wbA and Okay, then clear the ‘edit links’ dialogue box (the link to wbB will have disappeared)
  [/list]

Reply | Quote

Worked perfectly! Thanks! I appreciate the help… I love it when I learn something new…

Reply | Quote