Hello,
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.
Thank you.
_______________________________
Sub symbolloop()
' Loop through symbols
Dim PauseTime, Start
Dim intRow As Integer
Dim intMaxRows As Integer
intMaxRows = Cells(70, 1)
intRow = 1
Do While intRow <= intMaxRows
'MsgBox intRow
Range("C11").Select
ActiveCell.FormulaR1C1 = "=[CurrentlyOwnedStocksDB.xls]Sheet1!R" & intRow & "C1"
'Application.OnTime Now() + TimeValue("00:00:03"), "SaveCopy"
intRow = intRow + 1
Loop
End Sub
Sub SaveCopy()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
'run regression analysis
Application.Run _
"'Master 20YR Dividend Regression actual - updated.xls'!Aregression20"
Application.Run "RefireBLP"
' save copy
With Application
.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
Sheets(Array("Chart")).Copy
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
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select
' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
' 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"
ActiveWorkbook.Close SaveChanges:=False
End With
End Sub
