Hi all

I am trying to write a macro which will copy a worksheet from one Workbook to another using the Worksheets collections Copy method. The source Sheet has formulas which refer to other sheets cells in the Source workbook. The Target workbook also has the same set of worksheets (with the same name) except the one that I am copying. After copying, what I noticed is that the copied sheet still refers to the Source workbook's sheets in all formulas rather than the one present in the current workbook. How do I make it refer to the current workbook and not the source workbook

for e.g.

Source work book (abc.xls) has Sheet1, Sheet2, Sheet3. There is a formula in the Sheet3 which is "=Sheet1!C3*1.12". The target work book (xyz.xls) has Sheet1 and Sheet2. When I open both the workbooks and select the Sheet3 of the abc.xls and say copy to the xyz.xls, the formula in the sheet3 of the xyz.xls becomes "=[abc.xls]Sheet1!C3*1.12". What do I need to do if it has to remain like "=Sheet1!C3*1.12" and not refer to the source file.