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 ...
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.Here is what I was doing before the error in Excel 2000. I received the error on the .Cells.Replace line: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 = TrueCode: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
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 2010 (mainly), 2016 (not Office 365), 2013, 2007, 2003 - 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