copy and pasting issue

[chad1222]

Ok here is the deal i have a sheet with a ton of refences that are linked to other sheets on the same workbook. When those refences get messed up by my users i would like to just recreate the data sheet with all the formulas by copying a backup sheet and pasting it it. But when i do this it copys all the refences based on the backupworkbook.
example
Cell A1 = "hostname" & sheet1!A5

after pasting the backup sheet ontop of the old one it comes out
Cell A1 = "hostname" &[backupworkbook!sheet1.xls]sheet1'!A5

how can i make it just paste in the EXACT formulas

thanks

Chad

 

[chad1222]

bump

 

[BrianB]

Hopefully this will help to set a principle :-

'----------------------------------------------------
Sub test()
Dim MySheet As Worksheet
Dim MyFormula As String
'--------------
Set MySheet = Worksheets("MySheet")
For r = 1 To 20
MyFormula = MySheet.Cells(r, 1).Formula
ActiveSheet.Cells(r, 1).Formula = MyFormula
Next
End Sub
'----------------------------------------------------