Time based formula

[kiran2512]

Hi, Can any one help me with a formula that enables excel to copy a value in a cell at a particular time of the day.

for example: A1 is having value which it gets from an external web source and is being refreshed every minute. i want the value in A1 at 10:00 AM every day to be automatically copied to A1 in another sheet of the same file. Is this possible. Please provide me with the code for this type of function if any one has it...

 

[kiran2512]

I am able to get the values in the required cells at the time given in the Code...I have a requirement of getting values at two different times of the day to different cells.

As i am getting values of E1 and F1 of sheet 5 to the next empty cells in columns I and J in sheet 2 at 9:45:00 every day, i require values of D1, E1, F1, G1 of Sheet 5 to next empty cells of columns B, C, D, E of Sheet2 at 16:00:00 every day. How do i modify the above code to get the desired result...Plz Help...

 

[Rmarvin]

I am able to get the values in the required cells at the time given in the Code...I have a requirement of getting values at two different times of the day to different cells.

As i am getting values of E1 and F1 of sheet 5 to the next empty cells in columns I and J in sheet 2 at 9:45:00 every day, i require values of D1, E1, F1, G1 of Sheet 5 to next empty cells of columns B, C, D, E of Sheet2 at 16:00:00 every day. How do i modify the above code to get the desired result...Plz Help...

Private Sub:

Private Sub Workbook_Open()
    Application.OnTime TimeValue("09:45:00"), "CopytoNewSheet"
    Application.OnTime TimeValue("16:00:00"), "CopytoNewSheet"
End Sub

Module Sub:

Sub CopytoNewSheet()
If Hour(Now) = 9 And Minute(Now) = 45 Then
Application.EnableEvents = False
Worksheets("Sheet5").Range("E1").Copy Destination:=Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, "I").End(xlUp).Offset(1, 0)
Worksheets("Sheet5").Range("F1").Copy Destination:=Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, "J").End(xlUp).Offset(1, 0)
Application.EnableEvents = True
ElseIf Hour(Now) = 16 And Minute(Now) = 0 Then
Application.EnableEvents = False
Worksheets("Sheet5").Range("D1").Copy Destination:=Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, "B").End(xlUp).Offset(1, 0)
Worksheets("Sheet5").Range("E1").Copy Destination:=Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, "C").End(xlUp).Offset(1, 0)
Worksheets("Sheet5").Range("F1").Copy Destination:=Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, "D").End(xlUp).Offset(1, 0)
Worksheets("Sheet5").Range("G1").Copy Destination:=Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, "E").End(xlUp).Offset(1, 0)
Application.EnableEvents = True
End If
End Sub