Copy worksheet from file to file without the links, in Excel 2010

After copying a sheet from Excel File1 to Excel File2, all the functions on the copied sheet in File2, will be linked to File1.

For example, there are two Excel files. Each file includes two sheets. In each file the names of the two sheets are identical. One sheet named Sheet-A the other sheet named Summary.

After copying the Summary sheet from File1 to File2, the content of a cell, in the sheet Summary that has been just copied to File2, will look like this:

=VLOOKUP(A10,'[FILE1.xls]Sheet-A'!$D$2:$K$100,5,0)

As can be seen the data in Summary sheet of File2 is taken now from Sheet-A of File1 while we want it to be taken from Sheet-A of File2.

If the links to File1 will be removed the data in the Summary sheet of File2 will be taken from Sheet-A of File2 its self and not from File1.

After removing the links, the content of a cell in File2 will look like this:
=VLOOKUP(A10,'Sheet-A'!$D$2:$K$100,5,0)

Now the VLOOKUP function takes the data from Sheet-A of File2.

Option 1

Remove the text [FILE1.xls] from all formulas in the sheet Summary of File2 by the Find and Replace feature exist in Excel.

Option 2
Remove all the links from the sheet Summary of File2 through the Edit Links window:

1. Click the square at the top let corner of the sheet, left to column A, to select the entire Sheet.

                                                

2. Click the Data TAB

3. Click the Edit Links button

4. Click the Change Source button

        Pop up window will open to explore file locations

5. Go to the location of File2.xls and click the Open button

Clicking the Open button after File2.xls is displayed in the ‘File name:’ field.
The above window will automatically close and the next window will show up.

        The line with the link to File1.xls has been removed automatically.

6. Click the Close button to complete the process

Done, now all functions in File2 take the data from Sheet-A of File2.

* * *