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!

 

[amitsan]

Hi, i had same problem, but it works now after small adjustment
Range("B" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value

And big thanks to JB for code and instructions!

Thank you for you help...It worked perfect..

 

[Anubhav_g]

Hey JB!

I also need similar kind of help,as I don't have knowledge on macros and stuff !
I have 3 columns (C,G,H) in which values keep on getting updated automatically every second. Now I want to store the values from these columns in columns (I,J,K) respectively, at 9:20:00 AM ( not one second later or earlier than that, timing is extremely important) everyday. Only the values at 9:20:00 AM are needed.
For example:
From cells C7 to I7, G7 to J7, H7 to K7.

Will you be able to help me on this ? Waiting for your reply.

 

[amitsan]

Try this file

https://www.dropbox.com/s/mxp59zps7ugw9v1/Test-1.xlsm?dl=0


Change the required time in the VBA see attached SS

open link if ScreenShot not opening

https://www.dropbox.com/s/uhgfjr3qg3ucluw/test-1.png?dl=0

 

[Anubhav_g]

Hey Amitsan!!

Thank you so much for the prompt reply ! It works absolutely fine.
But, my mistake, I forgot to mention one important information; that I want it for 65 rows, that is starting from 7th row to 71st row.

So, value of cells, from C7-C71 to I7-I71, G7-G71 to J7-J71, and H7-H71 to K7-K71 respectively !

For simplicity can we do something which makes the macro to run automatically at 09:14:59 AM and stop at 09:20:00 AM. For stopping you've already done !

Asking so much from you ! :D Hopefully you will be able to help again !

Try this file

https://www.dropbox.com/s/mxp59zps7ugw9v1/Test-1.xlsm?dl=0


Change the required time in the VBA see attached SS

open link if ScreenShot not opening

https://www.dropbox.com/s/uhgfjr3qg3ucluw/test-1.png?dl=0

 

[Anubhav_g]

Hey Amitsan!!

By googling, i was able to find the answer to do it for the cell ranges.. i substituted cells(7,9).value with range(i7:i71). value. It works fine !

i am still unable to find codes on automatic starting of macro though !

i have to use this macro's code in some other macro enabled sheet (master sheet). when i did that (copy and pasted this code by inserting 2 new modules) and added buttons (from "form controls and assigning respective macros to it) an error came : "ambiguous name detected", this i suppose happened because i have another macro running with the same name.. is ti possible ? so what should i do it now ? i tried changing the name of "start" button to "hello", but now new error came: "cannot execute macro in break mode".

so for now i have deleted the modules from the master sheet. and now master sheet is working fine, no "ambiguous....." errors. but i am unable to add your macro in it !!!

Lots of help required !!!! too confused and brain dead.
wish i could send some gift as a token of thanks !

 

[Anubhav_g]

Hey Hey Amitsan !

It's done !

Problem solved ! No more errors !

I just had to change the names in the code also, earlier i just did it only in the buttons. it works fine now. thanks a lot ! would not have been possible without u ! let me know how can i be helpful to you ! i will do whatever is in my capacity ! suppppeerrr happy !

 

[amitsan]

Hey Hey Amitsan !

It's done !

Problem solved ! No more errors !

I just had to change the names in the code also, earlier i just did it only in the buttons. it works fine now. thanks a lot ! would not have been possible without u ! let me know how can i be helpful to you ! i will do whatever is in my capacity ! suppppeerrr happy !

I am glad that the problem got solved.
No need to do thank me.
Just don't break the chain...few months back I was also searching the solution for my requirement. I got that here...

 

[limbaddinesh]

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

Hello Sir,

The above code given by you is working perfectly fine when i am adding values manually.

Its not working for the cells whose values are changing with formula.

Can you please tell me how to make it useful with the cells changing values with Formula.

It will be a big help.

Thanks...

 

[jbeaucaire]

That's a different process. First we have to know which cell we're watching.

Let's assume the formula is in C2.
Let's assume the copy is going into Sheet2 in column A.

This code goes in the Sheet1 sheet module, and it triggers anytime any calculation is done by formula anywhere on the sheet. It compares the value in C2 to the "stored" value, it will copy the new value to sheet2 if it changes.

Option Explicit
Dim Prior As String

Private Sub Worksheet_Calculate()
    If Range("C2") <> Prior Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("A" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("C2").Value
        Prior = Range("C2").Value
        Application.EnableEvents = True
    End If
End Sub

You should be able to tweak from here...

 

[limbaddinesh]

It's Great Sir........

Thanks a Lot......