How do I stop sheet refs in formulas changing to #REF

[dave3944]

Please help me with my problem. Actually, I may have 2 problems.
I have a workbook ("Manpower") that has 12 sheets which do a variety of tasks. One sheet is called "Data", which contains over 2500 very detailed formulas. The formulas contain references to Sheet "CRP". This sheet is updated every week. I receive the updated sheet by e-mail. I open it, open workbook "Manpower". At this point, I want to:
1. Copy the new worksheet (called "TEST") to "Manpower".
2. Rename it to "CRP"
3. Delete the old "CRP"
without all of the 2500 sheet references in the cell formulas changing to #REF.

I switched to this approach because I wanted to transfer this whole process to another person who has Excel 2000 on his PC. I have Excel 2002. He kept receiving an error when the program got to the .Replace Method statement I was previously using.

An answer to either problem would free me from another duty. Thanks, in advance for any help. Below is the code I'm using to do this.

    For Each Workbook In Application.Workbooks
    Debug.Print Workbook.Name
        If Workbook.Name Like "*CRP*" Then
            myWB = Workbook.Name
            With Workbooks(myWB).Sheets(1)
                .Copy After:=ActiveWorkbook.Sheets("CRP")
            End With
            Exit For
        End If
    Next
    
    ActiveWorkbook.UpdateRemoteReferences = False
    Application.ScreenUpdating = False
    Worksheets("10 Week Analysis").EnableCalculation = False
    Worksheets("Data").EnableCalculation = False
    Application.DisplayAlerts = False
    ActiveWorkbook.Sheets("CRP").Name = "CRP2"
    ActiveWorkbook.Sheets("TEST").Name = "CRP"
    ActiveWorkbook.UpdateRemoteReferences = True

Here is what I was doing before the error in Excel 2000. I received the error on the .Cells.Replace line:

    Worksheets("10 Week Analysis").EnableCalculation = False
    Worksheets("Data").EnableCalculation = False
    Sheets("10 Week Analysis").Cells.Replace What:="CRP2", Replacement:="CRP", LookAt:=xlPart, _
        SearchOrder:=xlByRows
    Sheets("Data").Cells.Replace What:="CRP2", Replacement:="CRP", LookAt:=xlPart, _
        SearchOrder:=xlByRows
    Worksheets("10 Week Analysis").EnableCalculation = True
    Worksheets("Data").EnableCalculation = True
    Application.DisplayAlerts = False
    Sheets("CRP2").Delete
    Application.DisplayAlerts = True

 

[Peter_SSs]

dave3944

I haven't studied your code closely or tried to write replacement, but I have a couple of alternatives you might like to consider:

1. Replace the data not the sheet:
Have your macro delete all the data from "CRP" (not delete the sheet itself) and copy the new data onto the old "CRP" sheet from the "TEST" sheet. This should stop the #REF happening in the first place.

2. Fix the #REF! errors:
After you have replaced the old "CRP" sheet with the new one, have the "replace" part of your macro replace "#REF!" with "CRP!" on your "DATA" sheet