Simple Copy and Update Cell Value Every Minute

[user04]

I am trying to grab the value of a cell every time it updates and copy that value and store in a new cell.

So I have a cell (A2) and it updates every 60 seconds, I need that value for t=1 (first time) to be copy and pasted into a new cell (B2). Then when t=2 (second time, 60 seconds later) I need the value to be pasted into the same column but a new row, namely cell C2. Then when t=3 (third time), I need it to be copied to cell D2 and so on.

I'm assuming it just a macro that is called every 60 seconds and grabs the value of A2, but then needs to find the next cell in column B that is empty and paste it there. Is this right?

I've tried to look up for an hour now on how to do something like this but all the VB code I see doesn't really help me out. I'm assuming this should be a relatively simple operation, but just can't seem to find any help on it.

Any help is greatly appreciated!

Thanks!

 

[jbeaucaire]

Regarding The code you sent me for value changes every 5 seconds, JB, I do not know vb and any other softwares, so i do not know how to copy and paste this code and how to activate it. I tries to copy the code the way i did in previous code, but it did not work.

That's why my post #6 actually gives step by step instructions. If you follow them, it will work. If not, I can't help any better than that.

 

[jbeaucaire]

Thanks for your prompt reply JB. I will try to find out whats going wrong in my computer. I request you too llok in the matter when u get chance. I have one more request. can u write me a code which gives sound alert when the changing value of a cell reaches or crosses some specific value. for example i want a sound alert when value in A1 reaches to 275.
thanks
Sanjay Bhavsar.

Just add this:

Sub ValueStore()
Dim dTime As Date
    Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
    Call StartTimer
    If Range("A1") > 275 Then Beep
End Sub

If you want a fancier notification like a WAV file played, look at some other code like this:

http://www.exceltip.com/st/Playing_WAV-files_using_VBA_in_Microsoft_Excel/460.html

http://vbadud.blogspot.com/2008/08/play-audio-file-using-excel-vba.html

 

[fcc05]

thank you JB. I sure would give it a try.

 

[makinamati]

Rightclick on the sheettab and select VIEW CODE. Paste in this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
    End If
End Sub

JB, thanks for such great macros...
i need the macro above (quoted) to do what it does but to erase the top row every time it adds a new entry but scrolling all the values.

i.e.
adding updated information but posting it in only 30 rows so the information is not added forever to the rows below, in order to graph only the latest info from the 30 rows.

hpe this explains it.

JB, if this posible i would really apreciate the solution, and i even can send you funds via paypal as payment, because i really need this! you tell me.

 

[jbeaucaire]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NR as Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
   NR = Range("B" & Cells(Rows.Count).Row).End(xlUp).Row + 1
   Range"("B" & NR).Value = Range("A1").Value
   If NR > 30 Then Range("B1").Delete xlShiftUp
End If
End Sub

As per the private message I sent, a PayPal "thank you" gift would always be welcomed! Take care.

 

[makinamati]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NR as Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
   NR = Range("B" & Cells(Rows.Count).Row).End(xlUp).Row + 1
   Range"("B" & NR).Value = Range("A1").Value
   If NR > 30 Then Range("B1").Delete xlShiftUp
End If
End Sub

As per the private message I sent, a PayPal "thank you" gift would always be welcomed! Take care.

JB, i´ve tried it again and again and it keeps telling me that there's an error,
line 1 gets yellow highlighted -->
Private Sub Worksheet_Change(ByVal Target As Range)

and 5 gets red-->
Range"("B" & NR).Value = Range("A1").Value

i´m putting this code on the sheet: view code option-->VB-->paste->alt+q

please help..

 

[jbeaucaire]

Sorry, there is a typo on that line, take out the first quote:

Range("B" & NR).Value = Range("A1").Value

 

[mnordeen]

Jb,
That is sweet, I was looking for just what you had here.
thanks!

 

[huumingh]

Something else must be interfering. There's nothing in the code that's limited by time. It restarts the timer each time it enters a value...adds 5 seconds and counts again.

I don't use Excel 2007, so can't help troubleshoot that...but again, there's nothing in the code that shouldn't work the same in both.

Perhaps someone with Excel 2007 can offer some suggestions, the approach I've shown is a standard one.

Nice thread - some good stuff to consider.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[taprico]

Bhai too toh banda master mind hai --- was looking for this -- though not tailor made but just what I needed
Thanks a lot to U and the forum

I just registered to say THANKYOU to you