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