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...

 

[Rmarvin]

In the "ThisWorkbook" code, place this:

Private Sub Workbook_Open()
    Worksheets("Sheet1").Select
    Application.OnTime TimeValue("10:00:00"), "CopytoNewSheet"
End Sub

In a new Module place this macro:

Sub CopytoNewSheet()
Worksheets("Sheet1").Range("A1").Copy Destination:=Worksheets("Sheet2").Range("A1")
End Sub

Change the sheet names to suit.

In order for the code to run again the next day, the workbook will need to be closed and reopened.

 

[kiran2512]

In the "ThisWorkbook" code, place this:

Private Sub Workbook_Open()
    Worksheets("Sheet1").Select
    Application.OnTime TimeValue("10:00:00"), "CopytoNewSheet"
End Sub

In a new Module place this macro:

Sub CopytoNewSheet()
Worksheets("Sheet1").Range("A1").Copy Destination:=Worksheets("Sheet2").Range("A1")
End Sub

Change the sheet names to suit.

In order for the code to run again the next day, the workbook will need to be closed and reopened.

Thanks for the code..

I have tried the code you have provided... it does'nt work. I will provide you with the exact scenario.

The cells E1 and F1 of Sheet5 in my excel file gets updated with live data which it gets from the web.
Every day i want the data in E1 and F1 at 9:45 AM to be updated in Sheet 2, Column "i" and "j" respectively. The data has to get updated in a new cell in column "i"and "j" every day..

 

[Rmarvin]

Change the time in the ThisWorkbook code to this:

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

If I'm interpreting the last part of your comment you want the values of E1, and F1 on Sheet5 to be copied to Sheet 2. My question is do you want it in I1, and J1, or the next empty cell in those columns.
I have made a code for both of those scenarios here:

Only Copied to I1, and J1:

Sub CopytoNewSheet()
Worksheets("Sheet5").Range("E1").Copy Destination:=Worksheets("Sheet2").Range("I1")
Worksheets("Sheet5").Range("F1").Copy Destination:=Worksheets("Sheet2").Range("J1")
End Sub

To copy to the next empty row in I, and J:

Sub CopytoNewSheet()
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)
End Sub

 

[kiran2512]

Change the time in the ThisWorkbook code to this:

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

If I'm interpreting the last part of your comment you want the values of E1, and F1 on Sheet5 to be copied to Sheet 2. My question is do you want it in I1, and J1, or the next empty cell in those columns.
I have made a code for both of those scenarios here:

Only Copied to I1, and J1:

Sub CopytoNewSheet()
Worksheets("Sheet5").Range("E1").Copy Destination:=Worksheets("Sheet2").Range("I1")
Worksheets("Sheet5").Range("F1").Copy Destination:=Worksheets("Sheet2").Range("J1")
End Sub

To copy to the next empty row in I, and J:

Sub CopytoNewSheet()
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)
End Sub

I want the values of E1 and F1 of Sheet5 in the next empty cell of the columns "I" and "J" in sheet 2. I have tried the two codes which you have provided(by changing the time in the code to current time and refreshing the sheet).. no result...
i have tried the code on the existing sheet and also on a new sheet to test it... i am not able to get the values in sheet2 at the time specified in the code...

Does this work in any version of excel and do i have to set time anywhere in the sheet??

 

[Rmarvin]

I'm using 2010. I've tested each code that I've given you. Are you sure you're placing them in the right modules? Set the time to a minute or two in the future, sav3 thw workbook, then re open it. The code should run. You HAVE to save, close, then reopen for code to start.

 

[kiran2512]

I am using excel 2007.. will this work in this version??
Can u give me the step by step process of placing the code which you have followed and got the result so that i can cross check with the process i have followed...
Anyway thanks a lot for ur time...

 

[Rmarvin]

With the workbook open press Alt+F11 to open VBA editor. Under the objects double click the item that says "ThisWorkbook" place the code that starts with Private Sub in the text area that popped up. Change the 09:45:00 to a few minutes in the future (to test). If you already have a module for VBA then paste the third code I commented earlier. Save, and exit the workbook. Reopen the workbook and at the time value set the code should copy and paste the values needed.

 

[kiran2512]

It worked... Thanks a lot..
The mistake i made is that i have not saved the file as .xlsm format and was saving it as .xlsx format. After saving it in .xlsm format it worked..

 

[Rmarvin]

You're Welcome! Enjoy!