|OK, give this a try...|
The way this should work is as follows:
The Workbook_Open() code will schedule the LockPrices() code to run at 3:00 PM assuming the workbook is Open at 3:00PM and the focus of Excel is on that workbook.
Note: As far as I can tell from my testing, the system's focus does not have to be on Excel, but Excel's focus must be on the workbook with the macros. In other words, you can't be working in another workbook at 3:00PM because that will take Excel's focus away from the workbook that contains the macro you want to run at 3:00PM. There may be a way around that issue, but I don't know how to make it happen and don't have time to investigate it. If you want to Google around for a solution and point me towards it, I'll see what I can do.
So, back to the code...
The LockPrices() code will Copy the current last Row of Sheet1 to the Row below it. That will copy the formulas so that they can update the next day. After the formulas are copied to the Row below, the code will then lock the values in the Row above by using PasteSpecial...Values.
In my testing, I use =TODAY() in Column A to display the current date. That way when the formulas get copied, the TODAY() function goes into the new last Row along with the RTD formulas. For the rest of that day, i.e. after 3:00, the new last row will still reflect data for the current day but once the clock hits midnight the TODAY() function will update the next time the sheet calculates. As soon as the market opens, the RTD functions should begin to show the last market value for the securities. I did not test the RTD functions themselves, I simply used other formulas to test the concept.
In order to use these macros, they have to be placed in the proper modules. The Workbook_Open macro must be placed in the ThisWorkbook module. This is the code that will tell Excel to schedule the LockPrices macro to run at 3:00PM. The workbook must be Saved, Closed and then Opened in order for the scheduling to occur.
If you want to test the process, you can simply change the time within the quotes, as long as you Save, Close, and Open the workbook each time.
Private Sub Workbook_Open()
'Schedule LockPrice code macro to run at 3:00PM
Application.OnTime TimeValue("15:00:00"), "LockPrices"
The following is the code that will actually do the copying, pasting, etc. It needs to be placed in a standard module within the VBA editor (Insert...Module)
'Turn off ScreenUpdate
Application.ScreenUpdating = False
'Determine Last Row with data
lastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Copy last Row, with formulas, to next Row
Sheets(1).Cells(lastRow, 1).EntireRow.Copy _
Sheets(1).Cells(lastRow + 1, 1)
'Lock values for current day
Sheets(1).Cells(lastRow, 1).PasteSpecial Paste:=xlValues
'Turn off CutCopyMode to eliminate Copy lines
Application.CutCopyMode = False
'Turn on ScreenUpdate
Application.ScreenUpdating = True
Let me know how these work for you.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03