I am downloading live data from bloomberg and am attempting to write a macro that loops through several different stock symbols and saves the corresponding data for each symbol to its own invididual workbook. My problem is that I need to add a delay to my code in order for the live data to update before the rest of the code is implemented. Application.Wait will not work for this, nor will Sleep because these functions pause the execuation of the code.
I am using On.Time to to delay the processing of the rest of the code by 3 seconds. This works great when only processing one stock, but once I put the code into a loop, it flies through all of the stocks quickly and seemingly ignores my request to delay to code using On.time. I have been wrestling with this and tried just about every thing I can think of to pause the code. My code is below if anyone would like to look through it. There are two Modules (Symbolloop and SaveCopy). The ontime delay in symbolloop calls Savecopy.
' Loop through symbols
Dim PauseTime, Start
Dim intRow As Integer
Dim intMaxRows As Integer
intMaxRows = Cells(70, 1)
intRow = 1
Do While intRow <= intMaxRows
ActiveCell.FormulaR1C1 = "=[CurrentlyOwnedStocksDB.xls]Sheet1!R" & intRow & "C1"
'Application.OnTime Now() + TimeValue("00:00:03"), "SaveCopy"
intRow = intRow + 1
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
'run regression analysis
"'Master 20YR Dividend Regression actual - updated.xls'!Aregression20"
' save copy
.ScreenUpdating = False
' Copy specific sheets
' *SET THE SHEET NAMES TO COPY BELOW*
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas
On Error GoTo 0
' Paste sheets as values
' Remove External Links, Hperlinks and hard-code formulas
' Make sure A1 is selected on all sheets
For Each ws In ActiveWorkbook.Worksheets
Application.CutCopyMode = False
' Remove named ranges
For Each nm In ActiveWorkbook.Names
' Change Color Scheme to copied workbook so that colors/charts match
ActiveWorkbook.Colors = Workbooks( _
"Master 20YR Dividend Regression actual - updated.xls").Colors
' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs "F:\Test\" & "20YR Div Reg - " & Range("c11") & " - " & Format(Date, "mmddyyyy") & ".xls"