To prevent formula references from changing during copying, we can slightly modify formulas and then copy. You can do as follows:

1. Select the range in which you will copy the formulas, and select Range H1:H6 in our case, and then click Home > Find & Select > Replace. See below screen shot:

Note: You can also open the Find and Replace dialog box with pressing the Ctrl + H keys simultaneously.

2. In the opening Find and Replace dialog box, type = into Find what box, type a space into Replace with box, and then click the Replace All button.

Now a Microsoft Excel dialog box comes out and tells how many replacements it has done. Just click the OK button to close it. And close the Find and Replace dialog box.

3. Keep selecting the range, copy them and paste into the destination workbook.

4. Keep selecting the pasted formulas, and open the Find and Replace dialog box with clicking Home > Find & Select > Replace.

5. In the Find and Replace dialog box, type a space into the Find what box, type = into Replace with box, and then click the Replace All button.

6. Close popping up Microsoft Excel dialog box and Find and Replace dialog box. Now you will see all formulas in original workbook are copied exactly into the destination workbook. See below screen shots:

Notes:
(1) This method requires opening both workbook with formulas you will copy from and destination workbook you will paste to.
(2) This methods will modify the formulas in original workbook. You can restore the formulas in original workbook with selecting them and repeating the Step 6 and Step 7 above.

Easily combine multiple worksheets/workbooks into single worksheet/workbook

It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks!

Actually, we can quickly convert formula to text by Kutools for Excel's Convert Formula to Text feature with only one click. And then copy the text of formula to another workbook, and at last convert the text of formula to real formula by Kutools for Excel's Convert Text to Formula feature.

1. Select the formula cells you will copy, and click Kutools > Content > Convert Formula to Text. See screenshot below:

2. Now the selected formulas are converted to text. Copy them and then paste into the destination workbook.

3. Keep selecting the pasted text, and then click Kutools > Content > Convert Text to Formula to convert the pasted text to formula.

This method will introduce the Exact Copy utility of Kutools for Excel, which can help you exactly copy multiple formulas to a new workbook with ease.

1. Select the range in which you will copy the formulas. In our case, we select the Range H1:H6, and then click the Kutools > Exact Copy.p>

2. In the first opening Exact Formula Copy dialog box, click the OK button.

3. Now the second Exact Formula Copy dialog box opens, go to the destination workbook and select a blank cell, and click the OK button. See screenshot above.

Notes:
(1) If you can’t switch to the destination workbook, please enter the destination address such as [Book1]Sheet1!$H$2 into the above dialog box. (Book1 is destination workbook name, Sheet1 is destination worksheet name, $H$2 is the destination cell);
(2) If you have installed Office Tab (Have a free trial), you can switch to the destination workbook easily with clicking the tab.
(3) This method requires opening both workbook with formulas you will copy from and destination workbook you will paste to.

Sometimes, you may just want to copy and save a complex formula in current workbook, and paste it to other workbooks in future. Kutools for Excel’s Auto Text utility enables you to copy a formula as auto text entry, and let you reuse it in other workbooks with only one clicking easily.

1. Click the cell in which you will copy the formula, and then select the formula in the formula bar. See below screen shot:

2. In the Navigation Pane, click  in the far left of Navigation pane to switch to Auto Text pane, click to select the Formulas group, and then click the Add button  at the top. See below screen shot:
       

3. In the opening New Auto Text dialog box, type a name for the auto text entry of formula, and click the Add button. See screenshot above.

4. Open or switch to the destination workbook, select a cell, and then click the auto text of formula, the formula will be pasted to the select cell at once.

The auto text of formula can be reused at any time in any workbook with only one clicking.