For Loop not working; step-by-step does work

[HalPlz]

Hello all,

I use the code below to loop through a column of values and an external formula (meaning not provided from excel, an addon I downloaded) to download data for each of those values, and then compile the data in another sheet.

Sub TopManagerHoldings()

Application.ScreenUpdating = False

Dim x As Integer

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("TopHoldings")
  Set pasteSheet = Worksheets("PasteSheet")


For x = 2 To 4

Worksheets("TopHoldings").Range("B2:D4000").ClearContents

Worksheets("TopHoldings").Range("K2").Value = Worksheets("Funds").Range("A" & (x)).Value

Application.Wait (Now + TimeValue("0:00:03"))

Application.Calculate

v = Worksheets("TopHoldings").Range("C:C").Cells.SpecialCells(xlCellTypeConstants).Count

 copySheet.Range("A2:D" & v).Copy
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False

Next x

End Sub

When I run the code, no data is downloaded to be copy/pasted into the sheet. However, when I go through the code step-by-step (press f8), then it seems to work fine. Changing the amount of time or use of application.calculate doesn't seem to help. I've also tried changing the forumula auto-calculate feature off and on and screen updating. it seems like whatever I have in the for loop it doesn't iterate through, and does everything at once.

[Andy Pope]

Try moving the wait to after the calculate and add a DoEvents line or 2.

Worksheets("TopHoldings").Range("K2").Value = Worksheets("Funds").Range("A" & (x)).Value
Application.Calculate
Doevents
Application.Wait (Now + TimeValue("0:00:03"))
Doevents