capture live data at a specified time

I will have many cols whose cells are being constantly updated live.

I would like to be able to freeze/capture the data in the various cols at specified times.

ie ... col F at 13:00, col G at 13:10 etc ..

I would be grateful if anyone can help with that.

Regards

Paul

Thanks very much for your help ...

Could I just trouble you a little further to show how I would string the vba together to capture (copy) a multitude of times / cols ..

ie, I would possibly be looking to capture the data around 52 times a day, for example from 8am to 9pm every 15 mins, so col F at 8am, then col G at 8:15am, then col H at 8:30am etc ..
and I would be looking to copy the same cell from the data sheet to the destination sheet ie so F1 to F1 etc ..
Thanks again
Paul

Put this in the Workbook module:

Please Login or Register  to view this content.

And this in a regular module:

Please Login or Register  to view this content.

It will kick off at 8am (so long as you've opened the workbook before that time) and will copy F1 on Sheet1 to F1 on Sheet2. It will then run again every 15 minutes, copying the next column then the next etc., until the 53rd run at 9pm.

You can obviously tinker with interval times and number of iterations etc. but should point you in the right direction at least.

See attached for a working version.

PS. I've never had to do this before so I'm winging it and am sure someone will be along with a better way.

BSB

Here's a version set to start when you click the button on Sheet1 and run every 1 second so you can see it working in much quicker time.

BSB

thank you BSB, that appears to work just great, just one other thing, i need to copy the whole column, I'm not sure how to modify ..
thanks again
Paul

Change this line:

Please Login or Register  to view this content.

for this:

Please Login or Register  to view this content.

BSB

brilliant, thank you so much ...
Happy New Year ..
Paul

Glad I could help.

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

Happy New Year to you too

BSB

Apologies if this is the wrong thing to do, (as this thread is now solved) just an after thought really ...
would it be possible to update "sheet 2" at 15 min intervals from 8:00 onwards (as this code indeed does) but from a single col on "sheet 1"
ie "Sheet 1" col F is continually updating, could i then copy the col at 8:00 to "sheet 2" Col F, then at 8:15 copy the same col ("Sheet 1" F) to "Sheet 2" col G and so on ..
Thanks
Paul

Change this line:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

BSB

Actually, ignore the previous post. You want to paste just the values so replace the current TimePaste sub with this one:

Please Login or Register  to view this content.

BSB

thats brilliant, thanks once again ..
Paul

No problem. Happy to help

BSB

Hi, just discovered one small thing, wonder if it can be avoided ..
when the data is copied from one sheet to another, the destination sheet is moved to the "paste" area and remains highlighted, this can be a little troublesome when working or viewing another area when new data is copied. is there a way to avoin this or maybe have the sheet "go home" after the transfer ?
Works great otherwise though ... so still very usefull
Cheers
Paul

Have a look at adding:

Please Login or Register  to view this content.

To the start of the sub and setting it to True at the end.

Commuting at the moment so cannot test but when I'm home I'll log in and check.

BSB

once again, thank you very much .. works a treat ..
Cheers
Paul