How to record/save data @ a specific time?

[Cusuma]

Hi,

I have some stock prices in a sheet I want to save to another sheet (in the same workbook) each day at 4 pm. How do I write a macro that does that?

Say I have stock prices for company, A, B and C in Column A1, B1 and C1. Each day @ 4 pm I want these prices to be saved to a sheet so I have a database with historic prices. This is what I want the macro to do.

Many thanks
Cusuma

 

[gardnertoo]

First macro: the archive macro.

I've made the assumptions that your data is on worksheet Sheet1 and your archive will live on Sheet2. On sheet 2, enter the company names in cells A1, B1, and C1. In a Module, insert the following code:

Sub archive()
    With Worksheets("Sheet2")
        nextrow = Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(nextrow, 1).Resize(1, 3).Value = Sheets("Sheet1").Range("A1:C1").Value
        .Cells(nextrow, 4).Value = Date
        
    End With
End Sub

This will look for the first empty row below the headings and existing data, and fill in the current values in Sheet1 A1, B1, C1 to that row on Sheet2. It then puts the current date in column D of that row.


Second: scheduling the macro to run.

In the code window for This Workbook enter this code:

Private Sub workbook_open()
Application.OnTime "4:00 pm", "archive"
End Sub

I've further assumed that you close this workbook at night, and re-open it in the morning. If that's true, every morning when you open it, the macro named archive() will be schduled to run at 4:00pm. If you don't close and re-open the workbook, the next 4:00 pm acrchive cycle will NOT run.