How to automatically refresh formulas?

I have Sheet1 with "formula"
=MySheet!A1
in e.g cell A1.
Because MySheet does not exist in this workbook, I get #REF error.
Later when I add MySheet, the #REF error is still there.
But if I select A1 in Sheet1 and then click somwhere in the formula editor line and press ENTER, then A1 changes to 0 (or existing A1 value in MySheet)

I wonder how to automatically refresh some/all formulas in Sheet1 that reference to MySheet, after MySheet is inserted.

Please, see also attached file.

This might help (I'm not sure). Pressing the F9 key on your keyboard recalculates the sheet for you. That should get rid of the error.

Try it and report back.

This way seems to work
=INDIRECT("mysheet!"&"A1")

Originally Posted by arthurbr

This way seems to work
=INDIRECT("mysheet!"&"A1")

It really does! Thank you!

Glad to help and thx for the feedback

Originally Posted by sfsalvo

Here's a macro that bangs through all the formulae in all the sheets in the Active Workbook and forces an update. It's like so extremely cool I can't hardly even believe it.

It resolves all the annoying #NAME? and #REF! errors before your very eyes, unless the formula is actually incorrect. It only touches cells with formulae.

Sub UpdateFormulae()

    On Error Resume Next
    
    Dim ws As Worksheet
    Dim rRange As Range
    Dim curCell As Range
    
    For Each ws In ActiveWorkbook.Worksheets
        
        ws.Select
        Set rRange = ws.Cells.SpecialCells(xlCellTypeFormulas)
        
        For Each curCell In rRange
            curCell.Select
            curCell.FormulaR1C1 = curCell.FormulaR1C1
        Next
            
    Next ws
    
End Sub

I got bits and pieces of the code from hither and thither in the Great Encyclopedia of the Community of Coders. I don't wear a hat but if I did, it would be off to them.

Works great if you don't have many formulas. When you have hundreds/thousands of cells that contain formulas, you might need to set a day aside while it works through.

Below is updated to turn off screenupdating, which will significantly increase performance. Worked in about two minutes for >1500 linked cells using indirect function.

Sub UpdateFormulae()
Application.ScreenUpdating = False
    On Error Resume Next
    
    Dim ws As Worksheet
    Dim rRange As Range
    Dim curCell As Range
    
    For Each ws In ActiveWorkbook.Worksheets
        
        ws.Select
        Set rRange = ws.Cells.SpecialCells(xlCellTypeFormulas)
        
        For Each curCell In rRange
            curCell.Select
            curCell.FormulaR1C1 = curCell.FormulaR1C1
        Next
            
    Next ws
    Application.ScreenUpdating = True
End Sub

Thanks for the code to refresh formulas, very useful!

Thank you very much for the code... its is really very useful...

How would I rewrite the macro above if I just want to refresh the formulas on the active sheet. In other words, I want to put a button on the active sheet that when pressed, would refresh all formulas on that sheet only. Sorry, probably a simple answer but I am new to the VB language.

Thanks.

I believe you would get rid of the "

For Each ws / Next

" portion, and

set ws = ActiveWorkbook.ActiveSheet.

Basically that would eliminate the loop through every worksheet in the workbook and activesheet is a built in property.

Sub UpdateFormulae()
Application.ScreenUpdating = False
    On Error Resume Next
    
    Dim ws As Worksheet
    Dim rRange As Range
    Dim curCell As Range
    
Set ws = ActiveWorkbook.ActiveSheet
        
        ws.Select
        Set rRange = ws.Cells.SpecialCells(xlCellTypeFormulas)
        
        For Each curCell In rRange
            curCell.Select
            curCell.FormulaR1C1 = curCell.FormulaR1C1
        Next
            
    Application.ScreenUpdating = True
End Sub

Hi All,

Sorry to resurrect this old thread.

This is a really helpful bit of code, so thanks for posting it up!!

I'd like to know if it is possible to set it to only update one cell in each worksheet, A1 for example?

Basically I have a workbook with about 50 worksheets and I have a bit of code that pulls the colour as well as the text through to linked cells, but need to refresh one cell in each worksheet for it to update.

The posted formula works perfectly as it is bt takes a couple of minutes to do it, if I could only update one cell per worksheet it would be much quicker.

I know 90 seconds isn't a massive amount of time, but every little helps ay.

Thanks very much for your help.

You would need to change the rRange variable as follows:

set rRAnge= ws.Cells(1,1)

At least that's what it would be for A1. You could also use the following:

set rRAnge = ws.Range("A1")