bulk re-enter formulas in cell to fix #NAME? error

[jackms]

We have a process where we end up with a workbook with formulas full of references to range names that don't exist in that workbook. As expected, the cells all show #NAME?
We then move the sheets from that workbook to another workbook where those names do exist.
EXAMPLE: WorkbookA has formula =RangeNameA but that range name does not exist in WorkbookA
But then we move the sheets from WorkbookA into WorkBookB. And RangeNameA does exist in WorkbookB

Problem is that even after moving the sheets, the cells still show #NAME? CalculateFull and CalculateFullRebuild don't fix the errors. The only way I know to fix this is to re-enter the formula in each cell.

For Each c In ws.UsedRange.Cells
    c.Formula = c.Formula
Next c

But there are many, many cells and this method takes too long. Is there a way I can fix the #NAME? in bulk for all the cells in these sheets?

 

[maabadi]

Are you test:

Calculate

or

ActiveWorkbook.calculate

Activesheet.calculate

 

[jackms]

Yes, I tried all those. I also tried CalculateFull and CalculateFullRebuild and that didn't help either.

 

[maabadi]

Select All Range have error.
Go to Replace (CTRL + H ):
Replace Rangename with 'Rangename'
and replace all

 

[mikerickson]

How do you move the sheets?
If you use Sheet.Copy to move, I believe that the new formulas will look to the original book for their Names.
If you use

newBook.newSheet.Range("A1").Formula = oldBook.oldSheet.Range("A1").Formula

that might work the way you want it to.

 

[jackms]

Select All Range have error.
Go to Replace (CTRL + H ):
Replace Rangename with 'Rangename'
and replace all

That quite a good idea. Problem is there are hundreds of different range names that the formulas refer to

 

[maabadi]

Go to Excel Option
Formulas
Change Workbook Calculation to Manual and save
and again Workbook Calculation to Automatic

 

[jackms]

Go to Excel Option
Formulas
Change Workbook Calculation to Manual and save
and again Workbook Calculation to Automatic

Sorry, didn't work

 

[jackms]

How do you move the sheets?
If you use Sheet.Copy to move, I believe that the new formulas will look to the original book for their Names.
If you use

newBook.newSheet.Range("A1").Formula = oldBook.oldSheet.Range("A1").Formula

that might work the way you want it to.

Thanks. Problem is, that since these are large sheets, I need something more efficient than looping through one cell at a time.

There may be some confusion about my issue. Here are the steps to reproduce
1) Workbook1: Add range name "MyRangeName" to a single cell
2) Workbook2/Sheet2: Enter formula =MyRangeName
3) Move Sheet2 to Workbook1
4) Formula still shows up as #NAME?

 

[GWteB]

Have never had a similar problem but this may work for you.
Put the names of the sheets with the missing reference to the named ranges in the array.

Sub UpdateFormulas()

    Dim oWs As Worksheet, shts, f

    shts = Array("Sheet1", "Sheet5")        ' << change to suit

    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    shts = Join(shts, ":")
    For Each oWs In ThisWorkbook.Worksheets
        With oWs
            If InStr(LCase(":" & shts & ":"), LCase(":" & .Name & ":")) > 0 Then
                With .UsedRange
                    f = .Formula
                    .Formula = f
                End With
            End If
        End With
    Next oWs
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub