Recording contents of cells into multiple columns

[pillory]

Hi all, I was given this answer by Tony H to my question about recording the contents of a cell which is linked to an IP address every 15 minutes into a column and it works. I would now like to record 12 different IP addresses into columns for Temperature and Humidity of Data halls but I'm stuck. Can anyone help please.
Code:
Sub GetData()
Dim rng As Range
Dim i As Long
Dim dte As Date
Dim dteNext As Date

dte = Now
Set rng = activesheet.Range("E1")
For i = 1 To 3 'change this to number of time to loop
ThisWorkbook.RefreshAll
DoEvents
Set rng = rng.Offset(1)
rng = Now
rng.Offset(, 1) = activesheet.Range("B3")
dteNext = dte + TimeSerial(Hour:=0, Minute:=1 * i, Second:=0) 'change this value to number of minutes
Application.Wait dteNext
Next
MsgBox "done"
End Sub
Code:

[tony h]

A note on Forum Rules: when posting code you must use code tage. That is you start your code with (but without the space) [co de] and end with [/co de]

I presume you have set up the links as before. I have added in a couple of example lines. It stores all the values in adjacent columns

Sub GetData()
    Dim rng As Range
    Dim i As Long
    Dim dte As Date
    Dim dteNext As Date
    
    dte = Now
    Set rng = ActiveSheet.Range("E1")
    For i = 1 To 3 'change this to number of time to loop
        ThisWorkbook.RefreshAll
        DoEvents
        Set rng = rng.Offset(1)
        rng = Now
        
        ''
        ''change this block to the actually addresses you need
        ''
        rng.Offset(, 1) = ActiveSheet.Range("B3")   '1st piece of data
        rng.Offset(, 2) = ActiveSheet.Range("C5")   '2nd piece of data
        rng.Offset(, 3) = ActiveSheet.Range("B12")  '3rd piece of data
        
        dteNext = dte + TimeSerial(Hour:=0, Minute:=1 * i, Second:=0) 'change this value to number of minutes
        Application.Wait dteNext
    Next
    MsgBox "done"
End Sub

[pillory]

tony h. Thanks for your help and the solution you gave me seems to work returning all 12 temperature and humidity readings from the IP addresses of the building management system into adjacent boxes that I can then colour code for in or out of limits, but the Excel sheet seems to lock up so that as soon as I run the Macro you gave me the numbers returned by the IP addresses freeze and I get row after row of the same readings instead of dynamic readings until the Macro has completed. Do you have any Ideas whats causing this and how to overcome it. Thanks.

[tony h]

deleted: see my next post

[tony h]

Ok, I have had a bit of a look and I think the code below should do the job.
What you do need to do is make sure that the connection is set with the "enable background refresh" to "off" ie un-ticked.
: on the data tab ... go to connections ... existing connections and pick properties

Sub GetData2()
    Dim rng As Range
    Dim i As Long
    Dim dte As Date
    Dim dteNext As Date
    Dim iMax As Long
    Dim wkb As Workbook
    
    Set wkb = ThisWorkbook
    dte = Now
    Set rng = ActiveSheet.Range("M1")
    iMax = 3 'change this to number of time to loop
    For i = 1 To iMax
        wkb.RefreshAll
        
        
        rng = Now
        
        ''
        ''change this block to the actually addresses you need
        ''
        rng.Offset(, 1) = ActiveSheet.Range("B3")   '1st piece of data
        rng.Offset(, 2) = ActiveSheet.Range("B15")  '2nd piece of data
        rng.Offset(, 3) = ActiveSheet.Range("B16")  '3rd piece of data
        
        Set rng = rng.Offset(1)
        dteNext = dte + TimeSerial(Hour:=0, Minute:=1 * i, Second:=0) 'change this value to number of minutes
        'Application.Wait dteNext
        If i <> iMax Then Do: DoEvents: rng = Now: Loop Until Now >= dteNext
    Next
    MsgBox "done"
End Sub

[pillory]

Thanks tony h for your huge effort to help. I am now off work until friday due to my shift pattern and so wont be able to try out your new code until then. As you can tell I am no programmer but will let you know how it goes over the weekend.