Copy paste macro with a loop for multiple sets of data based on value from one column

[MarekSwiderski]

My dear community,

I am struggling to write a macro that will do the following steps and I would like to ask for your ideas:
I would like the macro loop the same steps for addresses marked as 1,2,3,4,..10 all the way to the highest value in 'Addresses' column A.

Step 1.
Copy data from 'Addresses' sheet, column C and D
Step 2.
Paste them in 'Calc, (C13:D17) , then wait for 10 sec for an API to pull data and display results in (C35:D36)
Step 3.
Copy data from 'Calc' (B35:D36) and paste as values in 'Addresses', Columns E:G, in the first two rows where where the same Set value from column A of the currently processed pair of addresses is found.

I have uploaded the file, however I disabled the mentioned API, so the macro for now will copy paste the same Distance and Travel time.

 

[JB2020]

This should work. I'm not sure about whether Wait() will prevent your API from running so you may want to try replacing it with Application.calculate or DoEvents if not.

Sub CopyAddresses()

    Dim MaxIndex As Long, Index As Long
    Dim CopyRange As Range
   
    MaxIndex = Evaluate("=MAX(Addresses!" & Sheets("Addresses").UsedRange.Columns(1).Address & ")")
   
    For Index = 3 To MaxIndex * 5 Step 5
        Set CopyRange = Sheets("Addresses").Cells(Index, 3).Resize(5, 2)
        CopyRange.Copy Sheets("Calc").Range("C13:D17")
        Application.Wait (Now() + TimeSerial(0, 0, 10))
        Sheets("calc").Range("B35:D36").Copy CopyRange.Offset(, 2).Resize(, 3)
    Next Index

End Sub