Merge Excel Workbooks

I have two completly different workbooks.

Both of them have 3 worksheets with formulas linking between each sheet.

I want to merge the two books, keep the formulas between the sheets intact but not linked out of the new six sheet workbook to the original two workbooks.

Any suggestions?

 

Provided that the sheets in each book are uniquely named (i.e. right click sheet tab, rename them to anything you want, but differently in the two books, instead of the default sheet 1 etc.) the links will be preserved if you merge the two workbooks (it may depend on how the links were created, whether they include the file name or just the sheet name; I create the links from one sheet to another by copy a cell or range in the first sheet, then use paste special paste link where I want the link; that just uses the sheet name and, handily, automatically adjusts if you rename the sheet from which you copied).

You could try ASAP utilities a free add on for Excel with lots of useful functions, It has an import Excel file function. So you would open a new workbook (not the two books you want to merge), use ASAP utilities to insert your two workbooks (then if you wish, delete the redundant sheets of the new workbook).

http://www.asap-utilities.com/

Or, the basic Excel method is to copy the sheets into a new workbook:

Open both workbooks.

Right click on the first sheet's tab in Workbook 1, click copy or move.

In the box that opens, in the dropdown menu select new book (which will create a new book, probably called book3 if you just opened the two workbooks).

To keep the Workbook 1 intact, check the box create a copy, click OK.

Switch back to workbook 1, right click on the second sheet, select copy or move.
In the dropdown box select the new book
check the box create a copy, select where you want it relative to the first sheet you created in the new book, click OK.
Repeat for all the sheets in Workbook 1.

Repeat for workbook 2.

The new Workbook then contains all the sheets from the two workbooks. Save as suitable name.

 

You can do what JRosenfeld suggested however the links to the original sheets will be maintained.

What I would do is:
We'll call the two original workbooks Workbook_1 and Workbook_2 it does not matter which is which.

1. make sure that not of the worksheets has the same name, and rename FIRST if needed, and save both originals if changes are made.

2. In one of the workbooks (it matter not which one, we'll say workbook_1), select one of the sheets to be copied.
Click EDIT>Move or copy sheet...
In the dialog box select workbook_2 in the "To book:" drop down
Put a check mark in the "Create a copy" box.
Click OK

3. When you do this, Excel automatically gives workbook_2 the focus so click back to workbook_1
DO NOT SAVE THE FILE YET (VERY IMPORTANT)

4. Repeat step 2 and 3 for each of the remaining sheets you want copied

5. When you've copied the last of the three sheets to Workbook_2, and Excel has given Workbook_2 the focus, click FILE>SAVE AS...
Give the workbook a new name (we'll say Workbook_1_2).

6. Now you'll see that the only two workbooks open are workbook_1 and workbook1_2.

7. Close Workbook_1

8. Now working in the newly created Workbook_1_2
Click EDIT>Links
This opens a "EDIT LINKS" dialog box
You'll note there are link(s) directing back to Workbook_1 so click to highlight it and then click the "Change source" button.
This will open a standard looking "File open" dialog box
Direct it to the path for the NEW workbook_1_2 file and double click the file named Workbook_1_2
Back in the "EDIT LINKS" dialog box Click "CLOSE "

9. click File, save

This should now be complete.
YOu should have the two original, and unchanged workbooks (still linked to each other), and the NEW workbook which is completely independant of the original two.

 

If you merge the files with ASAP by importing both of them into a new workbook, then there is no need to edit the links, provided, as I said, all the sheets have unique names.

Example: in Workbook1 sheets are named A, B, C. on B there is a link to cell A1 on sheet A, which would be called A!$A$1

That link name is preserved in the combined workbook, so references the sheet A in that book, not the original workbook1.

 

Oh, yes, I knew the ASAP utility would do it, but I was referring to the lower half of you post where you referred to the standard Excel method.

As for ASAP, I've guess I've just never been a big fan of theirs.
It's nothing really that you can't use VBA or Macros to do (assuming you know how to write VBA or Macros or are willing to learn).
And I nothce the price is now up to $49.00!

 

It's FREE for home use, though you can donate if you wish.

I agree one could write one's own macros, but having them in that utility saves the bother.