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!

 

[bpond]

Thank you very much jbeaucaire.

You have always been so kind :')

With respect,
Kittikorn

 

[arunrana87]

Hello JB,
This code definately works. I have 2 laptopts. in one office 2007 and in another office xp is there. in office 2007 the code stops working after giving value from B2 to B64. and in office Xp it stops working after giving values from B2 to B256. i think we need to modify the code so that we can get this code work for 6 hours which is the stock market timing. thanks if u can check it and let me know what to do about it.

thanks

Sanjay Bhavsar

Re:-

I use the above code and face the same problem it can be short out if we put any number with B like B10000, so it can be work till that.

 

[mrjixies]

Hello JB,
This code definately works. I have 2 laptopts. in one office 2007 and in another office xp is there. in office 2007 the code stops working after giving value from B2 to B64. and in office Xp it stops working after giving values from B2 to B256. i think we need to modify the code so that we can get this code work for 6 hours which is the stock market timing. thanks if u can check it and let me know what to do about it.

thanks

Sanjay Bhavsar

Hi this macro is excellent!!! Thanks jb!!! But however I've go the same problem as fcc05. The macro is stopping at B64??? I've got the latest excel version i dont know what's going on???

 

[mrjixies]

I've put the timer on 1 second, and you see B1 filled till B64 then it wont go further to B65 instead it goes to B3 and keeps changing that cell???

 

[mrjixies]

I've found the problem. It seems in newer excel versions if you use

[COLOR=#242729][FONT=Consolas]Cells(Rows.Count).Row[/FONT][/COLOR]

is value 64. I've found this on another forum:

<code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Cells(Rows.Count).Row</code> is 64 in modern versions of Excel files. That is because <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Rows.Count</code> will be 1048576, and therefore <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Cells(1048576)</code> is referring to cell <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">XFD64</code> which is on row 64. Your code needed to specify a column so that it looked at the 1048576th cell in one column.

So how can we continue and not stopping at 64???

 

[mrjixies]

I've found the answer!!! Use the code below for newer version of excel and it works like a charm!!! 1000 thanks to YowE3K from stackoverflow and ofcourse thanks to JB!!!

Option ExplicitPublic dTime As Date


Sub ValueStore()
    Dim dTime As Date
    Dim nextRow As Long
    'Calculate the row number once
    nextRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
    Range("B" & nextRow).Value = Range("A1").Value
    Call StartTimer
End Sub

 

[Kev23m]

Hi Everyone,
This forum is a great help for non techies like me.

I have used the above code and modded it a bit for my purpose . Need to test it tomorrow during working hours to see if it works.Just want to confirm the best solution.

I have multiple sheets in my excel , for the working I have made a New Sheet " New Sheet2". The 2 columns which i need are copied using "=Sheet1!A1" & B1
The column B data keeps increasing and i want the difference every 5 minutes. I have data on B1:B98.

Thank You

 

[Maheswaran_P]

15.09.2019 :-

Dear Jbeaucaire & bpond, Exactly this is what i am searching. I am pulling data from web to excel2019. I need to record or save the price of particular colum every five minutes once in sheet2. i just need your help.

 

[Nijan Jyakhwo]

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 !

sir, Can you Please share that excel sheet.

 

[Nav678]

Hello JB,
This code definately works. I have 2 laptopts. in one office 2007 and in another office xp is there. in office 2007 the code stops working after giving value from B2 to B64. and in office Xp it stops working after giving values from B2 to B256. i think we need to modify the code so that we can get this code work for 6 hours which is the stock market timing. thanks if u can check it and let me know what to do about it.

thanks

Sanjay Bhavsar

Hey Sanjay, I just tried the same ...in 2007 excel after B64 its not working....did u get any alternative ..help

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!

Thanks a Ton .....You Solved it with simple adjustment