Loop not waiting for calculation before proceeding

I have a loop with 3 web queries that are brought into a renamed copy of a template worksheet. As this is part of a larger workbook I have the calculations set to manual. A block of formulas on the renamed worksheet are suppose to update. The block is then copied and the values pasted onto another worksheet. The renamed worksheet is then deleted and the whole process starts again. Everything works fine except that the formulas do not complete their update before they are copied and their values pasted when the loop is run. The calculations work perfectly when the loop is stepped though using F8.

I have tried various methods (wait, sleep, Application.CalculateUntilAsyncQueriesDone, DoEvents, turning the calculations to automatic, etc.) to allow time for the calculations to complete before the copy step is executed. There is nothing overly complex about the formulas (counts, some if statements, date calculations, and lookups). The formals update in less than a second when the loop is stepped though using F8.



A short snippet of the code is as follows:

Application.Calculation = xlManual
j = 1
Set MyRange = Sheets("Source").Range("D11")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange

Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value

ActiveSheet.Calculate
Sheets(MyCell.Value).Range("O2").QueryTable.Refresh BackgroundQuery = False
Sleep 200
Sheets(MyCell.Value).Range("AH2").QueryTable.Refresh BackgroundQuery = False
Sleep 200
Sheets(MyCell.Value).Range("AV2").QueryTable.Refresh BackgroundQuery = False
Sleep 200
ActiveSheet.Calculate

Sheets(MyCell.Value).Range("B3:L58").Copy
Sheets("Data").Select
Range("A3").Offset(0, (j - 1) * 11).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.DisplayAlerts = False
Worksheets(MyCell.Value).Delete
Application.DisplayAlerts = True

j = j + 1

Next MyCell



Any help is greatly appreciated.

Maybe I'm missing something, but is what you are suggesting any different from what I already have? Is it just a different syntax?

As an example: "Sheets(MyCell.Value).Range("O2").QueryTable.Refresh BackgroundQuery = False" appears to me to be functionally the same.

As a side note, I also made sure that none of the data connections "Enable background refresh" boxes were checked.