I am trying to merge 40 excel worksheets from different workbooks in
to a single workbook. All the sheets have cell references to other
workbooks (which are not being used for the merging process). To do
this, we have a vb application which moves the input sheets in to the
output workbook.

When i do this, all the sheets are merged properly except for one
sheet. In this sheet, there is a cell reference which multiplies 2
values (which are in different worksheets & are got using cell
references). When this sheet is merged, the cell reference changes.
this happens only in one cell. In other cells were there are similar
kind of references are giving proper output. Can someone please help
me to sort out this problem?