Capture Value from a Cell only once at a specific hour.

eeehhh

New Member
Joined
Feb 23, 2019
Messages
2
Hello Seniors,

I am looking to create a workbook as below:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sheet 1: Pulls data from web automatically say every 15 minutes.

Sheet 2: Takes data from Sheet 1 and calculates values.

Sheet 3:
B1 uses VLOOKUP function to pull data from Sheet 2.
C1 takes value from B1 and uses ROUND function to calculate new value.

Sample file is ready with above already incorporated, but not allowed to attach. Sample file is available at https://www.filedropper.com/sampleb

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Requirement:

Sheet 3, Cell D1 copies value from C1 only once at a specific time say at 10:00 Hours.

After 10:00 hours D1 should not update value even if C1 value updates as a result of Sheet 1 getting auto-refreshed.

Before 10:00 Hours D1 should show Zero or No Data.

After 10:00 Hours D1 continues to show fixed value which was copied at 10:00 Hours.

In other words the value in D1 gets fixed/locked at 10:00 hours.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Posted here also but got no solution as of now. https://www.excelforum.com/excel-programming-vba-macros/1265545-capture-value-from-a-cell-only-once-at-a-specific-hour.html

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Kindly help.

Thanks,

Also posted here
https://chandoo.org/forum/threads/capture-value-from-a-cell-only-once-at-a-specific-hour.41021/
 
Last edited by a moderator:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
Here's the refresh/update part:

Paste this code into the "ThisWorkbook" object in the Microsoft Excel Objects list in the VBA project window:
Code:
Private Sub Workbook_Open()
  
        Call Refresh    

End Sub
The above sub will run automatically when the file is opened and call the Refresh sub (below) which in turn schedules Excel to run the Copy_C1Value_to_D1 sub (also below) when the system time reaches 10am, and update the value of D1 on Sheet3 - on the assumption that the file is kept open until at least the next occurrence of 10am. (For this to work all the time the file has to remain open.)

Paste the following code (2 sub's) into a standard module in the "Modules" list in the VBA project window:
Code:
Option Explicit

Sub Refresh()
  
    Dim strTime As String
    Dim strSub As String
    
    strTime = "10:00:00"
    strSub = "Copy_C1Value_to_D1"
    
    Application.OnTime TimeValue(strTime), strSub
    
End Sub

Sub Copy_C1Value_to_D1()
    
   With Sheets("Sheet3")
        .Range("C1").Copy
        .Range("D1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .Range("E1").Value = "Refreshed at " & Format(Now, "dd/mm/yy @ hh:mm:ss")
    End With

    Application.CutCopyMode = False
    Beep
    Beep
    
End Sub

Regarding the clearing of Sheet3!D1:
Before 10:00 Hours D1 should show Zero or No Data.
Below is some code that will do it, but the question is when?

Given that time is a continuous 24 hour cycle, at what time does the period "Before 10:00 Hours" start? Or the more direct question: at what time should the value in D1 be cleared?
Code:
Sub Clear_D1()

    Range("D1").ClearContents
    Application.CutCopyMode = False
        
End Sub
 

eeehhh

New Member
Joined
Feb 23, 2019
Messages
2
Thanks a lot, it is working.

Regarding: at what time should the value in D1 be cleared?

Say at 09:00
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
You may have already worked this out yourself, but if not, do the following to modify what I've previously provided to now cater for the clearing of your preserved value at 9.00am:

1. Add the following line to the ThisWorkbook_Open sub:
Call Clear_Timer​

2. Replace the Clear_D1 sub with the following two sub's:

Code:
 Sub Clear_Timer()
  
    Dim strTime As String
    Dim strSub As String
    
    strTime = "09:00:00"
    strSub = "Clear"
    
    Application.OnTime TimeValue(strTime), strSub
    
End Sub

Sub Clear()

    With Sheets("Sheet3")
    .Range("D1:E1").ClearContents
    .Range("E1").Value = "Previous value cleared on " & Format(Now, "dd/mm/yy @ hh:mm:ss")
    End With
    Application.CutCopyMode = False
    Beep
    Beep
        
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,200
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top