Excel Macro DO/LOOP not working after cells update from another application

Help please! I have a macro (below) that works OK when invoked manually for grabbing data from a few cells as they get automatically updated from another application. My objective is to detect when new data arrives and continuously update a historical data table for the last N data points. My problem is that I can’t get Do/Loop to do this automatically (as if the macro gets interrupted by the updates.)

In the code below I am downloading two variables-&-time/date (each updated at different time intervals).
-- The first two routines check if data is new and is so will update the historical data table.
-- “Sub DownloadUpdate()” works OK when I invoke it manually.
-- “Sub UpdateTable()” also works OK manually BUT ONLY ONCE, i.e. I can’t get Do/Loop to do this automatically

I sure would appreciate some guidance. (I’m pretty good with Excel but only a novice with macros.) Thanks guys,

Tom

PS – Here is my code:

Sub NewMinClose()
'


	


















	
' NewMinClose Macro
' Check to see if new Close data has arrived by comparing current & grabbed times
' If it is NEW then UPDATE THE TABLE and also paste the new data in the "grabbed" cells
' Range D13 is the TIME of the latestdata available
    If Range("D13") <> Range("D6") Then
    Range("D9").Value = "New"
    Else: Range("D9").Value = ""
    End If

' If data is NOT new then end this routine
    If Range("D9") = "" Then End
    
' If the data IS new then update table:
        ' Shift Minute data from the Second (table) Row up to the First Row
        ' Copy/Paste grabbed Minute data to the last Row
    
' Range B18-D26 will need to change: B18:G517 (for 500 data lines)
    Range("B18:D26").Select
    Selection.Cut
    Range("B17").Select
    ActiveSheet.Paste
    Range("B13:D13").Select
    Selection.Copy
' Range B26 will need to change to B517 in order to post on the 500th data line
    Range("B26").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B6").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
End Sub


Sub NewTick()
'
' NewTick Macro
' Check to see if new Tick data has arrived by comparing current & grabbed VALUES
        ' (Note, not compare times as for 1Min data because the "Time" format does not include seconds
' If it is NEW then UPDATE THE TABLE and also paste the new data in the "grabbed" cells
' Range E13 is the VALUE of the latestdata available
    If Range("E13") <> Range("E6") Then
    Range("G9").Value = "New"
    Else: Range("G9").Value = ""
    End If

' If data is NOT new then end this routine
    If Range("G9") = "" Then End
    
' If the data IS new then update table:
        ' Shift Tick data from the Second (table) Row up to the First Row
        ' Copy/Paste grabbed Tick data to the last Row
    
' Range E18-G26 will need to change: E18:G517 (for 500 data lines)
    Range("E19:G26").Select
    Range("E26").Activate
    Selection.Cut
    Range("E18").Select
    ActiveSheet.Paste
    Range("E13:G13").Select
    Selection.Copy
    Range("E26").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("E26:G26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E6").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub


Sub DownloadUpdate()
'
' DownloadUpdate Macro
    Application.Run "'Copy of dde - 1.xls'!NewMinClose"
    Application.Run "'Copy of dde - 1.xls'!NewTick"
End Sub


Sub UpdateTable()
'
' Update Table
    Do
    Application.Run "'Copy of dde - 1.xls'!DownloadUpdate"
    Loop
End Sub


 

Thanks for taking the time to read and comply with the rule .

Hi rodrigt,
Thanks for sharing and posting the solution.

Not quite solved yet - How can I stop the continuously running macro? Last night I was able to stop it by entering bogus code into the macro, but somehow that is not working today... And if I shut down and re-start the worksheet the macro auto starts. Note - Per prior dialog (above), this macro "...entails placing Macros in the Workbook (to run in the background) and a corresponding macro in the worksheet."

I've tried Ctrl-Break and F8 but the macro keeps running and thus I can't debug my code. This problem gets worse when the Macro goes into "compile" and the error windows pop-up continuously. Any suggestions? Thanks.

PS - I'm also having trouble using If-Then-Else with End in one of the conditional statements, but that would be off-topic for this thread. Once I search this forum I may need to start a new thread.