Excel VBA pausing for Bloomberg

[mbush2]

Hi,

I am new to VBA and trying to create a macro where it will begin by inserting today's date into a cell, then pause for roughly 35 seconds while the massive correlation matrix pulls all the correlations of the portfolio in, then takes an output value that is generated in the workbook from the filled correlation matrix (correlation coefficient) and copy's and pastes it in a different cell. Then it repeats this process again using "Today's date - 1" (yesterday's date), waiting 35 seconds again and taking the new output value and copy and pasting it below the previous one.

I know I am not close but here is what I have for code thus far (which does not work properly). Thank you so much for any help.

Sub Test()

Range("B73").Select
ActiveCell.FormulaR1C1 = "=TODAY()-B75"

For i = 1 To 4
Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:35"), "Test3"
Range("B75").Value = 0 + i
Next i

End Sub

Sub Test3()
Range("C171").Select
ActiveCell.Formula = "=B76"
ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues
End Sub