VBA script to enter values rows

[llikethat]

Hi All,

I have the following query

Cell Sheet1!(A1) has value that changes every 2 seconds. I want to store this cell data in Sheet2!(A:A) as and when the Sheet1!(A1) value changes. For example

Sheet2!(A1)=10
Sheet2!(A2)=14
Sheet2!(A3)=45
Sheet2!(A4)=32
...
...
...
...

In the above example, 10,14,45,32 are values from Sheet1!(A1) which was appearing in that cell every 2 seconds.

Thanks
Bharani

 

[Logit]

Paste this in the Sheet1 Module :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Sheets("Sheet2")
        .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Me.Range("A1").Value
    End With
    Me.Range("A1").Value = ""
End Sub

 

[Joe4]

How exactly are the values in cell A1 on Sheet1 being updated?
Is it a formula or a link to somewhere else?
If so, what is it?

 

[llikethat]

Paste this in the Sheet1 Module :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Sheets("Sheet2")
        .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Me.Range("A1").Value
    End With
    Me.Range("A1").Value = ""
End Sub

Thanks for the quick reply. Will try this out.

 

[llikethat]

How exactly are the values in cell A1 on Sheet1 being updated?
Is it a formula or a link to somewhere else?
If so, what is it?

The values are taken from an external data link and that data updates periodically. It's like network data.

Thank you.