Incomplete Calculation when running Macro

I'm tearing my hair out!

I have what should be a simple macro to refresh two data tables (external sources) and recalculate a 'dashboard' style worksheet.

If I manually refresh the tables and recalculate - it works.
If I manually step through the macro (F8) - it works
If I run the macro, it only partially recalculates. As soon as the macro finishes, I can press F9 and it will calculate again.

How can this be!?

I have tried to force a second calculation in the macro, but this doesn't work. Stepping through the macro the second calc isn't required (because the first one worked!) and when running the macro, it still ends leaving calculations outstanding.

Can anyone suggest why this might be or what I can to get around this?

Thanks
JB

For what it's worth, the code is below:

Sub RefreshData()


ActiveWorkbook.RefreshAll




Calculate




End Sub

Re: Incomplete Calculation when running Macro

Quote from humdingaling;790894

sounds like your not leaving enough time for refresh

Thanks for the suggestion, unfortunately it hasn't helped. I can see from the tables after the macro has ran that the tables have refreshed. It is really is just a case that certain formulae haven't updated.

There are three main types of formula on the dashboard;
Array formulae - These do recalculate
Vlookups - These do not recalculate
Direct cell references - These do not update.
- Does that give any clues about what might be going on?

Cheers
JB

Re: Incomplete Calculation when running Macro

Damn, I haven't come across the CalculateFull method before, my eyes just lit up like a Christmas tree - alas it has not worked though Same for CalculateFullRebuild too.

Even using another idea from the same thread -

Cells(2, 8).Formula = Cells(2, 8).Formula

- to reapply the formula in the cells which don't recalculate still doesn't work

I think I'm flogging a dead horse here, I am just going to have to turn on autocalculations and accept it is clunky as hell

Re: Incomplete Calculation when running Macro

Wow, breakthrough, I have found -

Application.CalculateUntilAsyncQueriesDone


Calculate

- Will perform the whole calculation! Hooray

Thanks for your help Humdingaling!

JB