Excel VBA Internal Linking Problem

[Evpat]

I'm transferring information from an old workbook to a new updated workbook.

As far as I can tell, the method I'm using works well for everything except one:
If old workbook has 2 sheets (sheet 1, sheet 2), and sheet 2 links to sheet 1, the new workbook wants to link to sheet 1 of the old workbook.

I need to know how to make the link relative to the new workbook and not read the old sheet, as I'm going to use the macro to delete the old workbook and replace it with the new.

Here's what I'm doing:

Workbooks(Currbook).Sheets(Currsht).Range("B2").Copy Destination:=Workbooks(Handwrit).Sheets(Currsht).Range("B2")

Your expertise is greatly appreciated.

Excel 2007, Windows XP

 

[Evpat]

Hey Excel Gurus!

I figured out what I needed for a single cell. The code below converts the old formula to a string then makes that string the value in the new workbook.

Can somebody tell me a way to do this for a large range without looping through every cell? I may have to resort to the loop, but I'm hoping not to so it doesn't get too slow.

I want to accomplish the same thing on B4:N40

        strFormula = Workbooks(Currbook).Sheets(Currsht).Range("B2").Formula
        Workbooks(Handwrit).Sheets(Currsht).Range("B2").Value = strFormula