Loop macro X number of times and restart.

[DButler1]

I have a macro that is capturing data once a minute. I would like the macro to run for 180 times, then start over for another 180 times. This is what I have so far, just need it to loop the 180 times... i think.

RunTimer = Now + TimeValue("00:00:10")
'=====================================
' Updates PI links


ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks


Application.OnTime RunTimer, "CopyData"
'=====================================
'Copy and move Data

' 1A

    Range("D10:D188").Select
    Selection.Copy
    Range("D11").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False
    Range("D4").Select
    Selection.Copy
    Range("D10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
' 1B

    Range("E10:E188").Select
    Selection.Copy
    Range("E11").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False
    Range("D5").Select
    Selection.Copy
    Range("E10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Yongle]

Is this what you want ?
When SetTimer is run, CopyData is run 180 times and then run again 180 times

Option Explicit
Private Count180 As Long, Run2 As Boolean

Sub SetTimer()
    Dim RunTimer
    If Count180 < 180 Then
        RunTimer = Now + TimeValue("00:00:10")
        ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
        Application.OnTime RunTimer, "CopyData"
        Count180 = Count180 + 1
    Else
        If Run2 Then
            Run2 = False
            Exit Sub
        Else
            Count180 = 0
            Run2 = True
            SetTimer
        End If
    End If
End Sub

Private Sub CopyData()
'copy the data
    PLACE CODE TO COPY DATA HERE
'start the timer again
    Call SetTimer
       
End Sub

 

[DButler1]

EXCELLENT!!! thank you.

Now, at the end of the 180 macro runs, I need to select the range D10:E189, and clear it so the new values can start populating for the 3 hours. Where would I need to insert this clearcontents line in the SetTimer code?

 

[Yongle]

    Else
        Range("D10:E189").ClearContents
        If Run2 Then

 

[DButler1]

Thank you, I will insert it in the morning and give it a whirl!

 

[DButler1]

MONEY!!!!! Works perfectly!

Thank you so much for your help. Much appreciated!!!

 

[Yongle]

Thanks for your feedback
Glad it worked