Results 1 to 2 of 2

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

This is a discussion on How do I stop sheet refs in formulas changing to #REF within the Excel Questions forums, part of the Question Forums category; Please help me with my problem. Actually, I may have 2 problems. I have a workbook ("Manpower") that has 12 ...

  1. #1
    Board Regular
    Join Date
    Jan 2005
    Location
    Gainesville, TX
    Posts
    139

    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.
    Code:
        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:
    Code:
        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

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    24,837

    Default

    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
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

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
  •  


DMCA.com