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

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

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

  1. #1
    Board Regular
    Join Date
    Jan 2005
    Gainesville, TX
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

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

    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
        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, _
        Sheets("Data").Cells.Replace What:="CRP2", Replacement:="CRP", LookAt:=xlPart, _
        Worksheets("10 Week Analysis").EnableCalculation = True
        Worksheets("Data").EnableCalculation = True
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True

  2. #2
    MrExcel MVP
    Peter_SSs's Avatar
    Join Date
    May 2005
    Macksville, Australia
    Post Thanks / Like
    25 Post(s)
    1 Thread(s)



    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
    Hope this helps, good luck.
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts