Help with taking a snapshot of a cell at specific time of day and copying to a sheet

I have a live data feed into Excel that changes all day. I would like to track a certain cell (which I have already have a formula) to copy that number to another sheet at 4:15 central time every Mon-Fri. Is there a way to do this?? Thanks! Mark

Hi
You can use the Application.OnTime vba statement for this. Such as:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:16:15"), "my_Procedure"
End Sub

This will call the macro "my_procedure" at 4.15. So you could record a macro to copy the required value to another designated cell and then call that macro in the procedure above. Replace the "my_procedure" with your own macros name.
The above procedured needs to be placed behind the worksheet and NOT in a module.
Hope this helps.
Good luck.
Tony

Hi Tony, okay, I am trying this:


Sub AvgAge()
Application.OnTime Now + TimeValue("00:16:15"), "avgage"
End Sub
Sub AvgAge1()
'
'

'
Range("B2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("C2").Select
ActiveCell.FormulaR1C1 = "='Average Age'!RC[6]"
Range("C3").Select
End Sub

I am confused on how i can automatically add the date and data from my cell. In other words, I want to create a column of the date with the data next to it and each day it automatically adds to the bottom of the data.

Hi Mark
Are you able to post a sample workbook, with anonymised data if neccessary, so I can see how you are referencing RC[6] on the Average Age worksheet.
Thanks
Tony

Hi Tony - thanks for the reply. I created a worksheet that shows what I am looking to do. I am new at macros and VB so thanks for your help!

Hi Mark
You will need to test this properly.
There i some code in the Module "This Workbook" page which runs when the Workbook is first opened.
Finges crossed it works.
Let me know.
Tony

Tony

Thanks! I will change the reference cell and see that happens at 4:15 today

Thanks again!!
Mark

I left the sheet open yesterday but it did not pull the figure at 4:15.

Dumb question, but in the VB it references "my_procedure" Is this something I am suppose to do separately?

Thanks - added that in. I changed the time to test it and nothing happened. Do I need to activate it somehow or will it just run automatically?

Hi Mark
Sorry for the delay.
Plesae see the attached workbook which I HAVE tested and it works now. That will teach me to stop trying to do things in a rush .
Again change the time for testing then change it back.
Hope this helps.
Tony

Thanks - when I run it manually (click the play button) it works. But, it does not run automatically if I change the date. I change the date to a few minutes out and close the VB window and watch and nothing happens. Is it a security setting??

Tony - actually, I realized i did not save and close and reopen. It works. How do I get it to create a column of info or add to the data each day so I can chart it? Right now, it just overwrites the cell

Mark

Hi Mark
I believe this is because Row 1 is empty. Just add something in C1 even if you change the font colour to White and it will work.
Hope this helps.
Good luck.
Tony

it works!!! Thanks so much!!

Thanks for the feedback Mark.

Is There a way to do this without having to close the worksheet?

I want something similar, except with numerous cells at numerous times of day....
attached is the one I am working with.
The 'Station Management' is the sheet that updates automatically and I want the cells in "Totals Worksheet' to contain the referenced cells' data from the specified time above.