Hi,

I have been tryng to use the macro below to copy data from A2 (which automatically changes) into B2, B3, etc at 5 second intervals.

A2 copies data from a cell in another sheet that automatically updates using the =Sales!F4 formula.

The problem is that rather than the code copying the content of A2 every 5 seconds it actually copies data from the Sales sheet offsetting by a row at each interval. So every 5 seconds it is copies G4, G5, G6, etc of the Sales sheet into B2, B3, B4, etc in the Raw Data sheet.

When I used the code previously it was run in the same sheet that the source data appeared so there was no equivalent of the =Sales!F4 link in the chain. It may be that is causing the issue?

Thanks,

James 

Option Explicit
Public StartTime, DelayInSeconds As Integer
Public gblnStop As Boolean
Function Pause(DelayInSeconds)
     '//this declares "Pause" to be a function that can be used in all other subs
    StartTime = Timer
    Do While Timer < StartTime + DelayInSeconds
        DoEvents
    Loop
End Function
Sub runTimer()
    gblnStop = False
    Do
        Copytosheet
        Pause 5
        If gblnStop Then Exit Do
    Loop
End Sub
Sub stopTimer()
    gblnStop = True
End Sub
Sub Copytosheet()
    Dim i As Long
    i = 0
    Sheets("Raw Data").Range("A2").Copy
    Do
        If Sheets("Raw Data").Range("B" & 1 + i).Value <> "" Then
            i = i + 1
        End If
    Loop Until Sheets("Raw Data").Range("B" & 1 + i).Value = ""
    Sheets("Raw Data").Range("A2").Copy Destination:=Sheets("Raw Data").Range("B" & 1 + i)
End Sub