Using ontime to plot data automatically

[prav78uk]

Hi

I basically need to instruct excel to automatically plot the cell
value of a given cell, each day at set time.

So the two values would be the cell value and the date.

The cell value on same days may be the same as the previous day.

I thought using Excel's Ontime function may help performing a Macro
that does this periodcially.

In the root module of the workbook I've added:

Private Sub Workbook_Open()13:00:00"), "MyMacro"
End Sub

On the spreadsheeet that has the value in A1 I've added the following
code to a new module:

Sub MyMacro()
Application.OnTime TimeValue("13:00:00"), "MyMacro"
Range("A1").Select
Calculate
ActiveWorkbook.Save

End Sub


I have only limited experiende of writing macros whatsoever and need
help on
writing a macro ("My Macro") that will look up the cell value, e.g.
A1, and add this to a list of cell values that have been obtained
alredy from previous days to a separate sheet.

Any help would be appreciated.

Many thanks

 

[ShaneDevenshire]

Hi,

Here is the basic code:

Private Sub Workbook_Open()
Application.OnTime TimeValue("20:47:00"), "myMacro"
End Sub

Sub MyMacro()
Calculate
Sheets("Sheet2").Range("A1").End(xlDown).Offset(1,0) =
Sheets("Sheet1").Range("A1")
ActiveSheet.Save
End Sub

The above code is just a sample but should go in the thisWorkbook object.
On the VBA screen, locate your file in the Project window, top left and then
double-click the thisWorkbook listed in the Microsoft Excel Objects folder
for your file.

The Workbook_Open code is correct, it is the myMacro code which is just a
sample.

To help you write the exact code we need to know a lot more, what are the
sheet names, where on the destination sheet are you putting the info from A1?

 

[prav78uk]

Hi,

Here is the basic code:

Private Sub Workbook_Open()
    Application.OnTime TimeValue("20:47:00"), "myMacro"
End Sub

Sub MyMacro()
    Calculate
    Sheets("Sheet2").Range("A1").End(xlDown).Offset(1,0) =
Sheets("Sheet1").Range("A1")
    ActiveSheet.Save
End Sub

The above code is just a sample but should go in the thisWorkbook object. 
On the VBA screen, locate your file in the Project window, top left and then
double-click the thisWorkbook listed in the Microsoft Excel Objects folder
for your file.

The Workbook_Open code is correct, it is the myMacro code which is just a
sample.

To help you write the exact code we need to know a lot more, what are the
sheet names, where on the destination sheet are you putting the info fromA1?

Thanks for your message.

The sheet that has the cell value that changes daily would be called
'2008 Summary'

In this cell, a1, is the value that changes


The 2nd sheet that copies the value from '2008 Summary' would be
called 'plot data'

This sheet would have the different values of a1 in column a. Column
b would have the corresponding dates of each value.

e.g. the first value would be in a2, and it's corresponding date would
be in b2.

I hope this makes sense