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

[eeehhh]

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/

 

[Col Delane]

Here's the refresh/update part:

Paste this code into the "ThisWorkbook" object in the Microsoft Excel Objects list in the VBA project window:

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:

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?

Sub Clear_D1()

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

 

[eeehhh]

Thanks a lot, it is working.

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

Say at 09:00

 

[Col Delane]

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:

 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