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?

 

[mikerickson]

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

This will move all the formulas to the new sheet, without loooping

newSheet.Range("A1:AZ999").Formula = oldSheet.Range("A1:AZ999").Formula

 

[jackms]

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

Great! It worked! Thank you!!!!

For future reference, here's the bit of code that contains the solution

                With .UsedRange
                    f = .Formula
                    .Formula = f
                End With

 

[GWteB]

You are welcome and thanks for letting us know.