Daily cell update

[Matt]

Here's what I'd like to do:

The value in cell A1 changes every day. Can that value be
automatically recorded each day into a list that compiles what the
value was each day?

For example, if on Monday A1 is 65, Tuesday its 57, Wednesday its 48,
then the list would look like so:

Mon 65
Tues 57
Wed 48

and so on.

Any ideas??

 

[Guest]

Hi Matt,

First question is do you want a macro to do this? If so then the possible
methodology is:-

A Macro to automatically run when the workbook is opened.

Initially the macro should compare the current date with a date saved
somewhere out of the way in the workbook.

If the current date is greater than the date saved in the workbook,
automatically run a process to append the previous day/date and the required
value to the list and then copy the current date to the saved date in the
workbook.

The above would then only allow the process to take place the first time the
workbook is opened on any day. All future opening of the workbook during the
same day will compare the current date and saved date as equal and so exit
the procedure.

If you would like me to write the macro as above then let me know. However
it will help if you can answer the following for me:-

Do you require instructions to copy the macro into the workbook? (It will be
a Workbook level event).

In the list created, will it be only weekdays or all days of the week? It is
possible to test for the previous day to check if Sat or Sun and use Fri in
lieu if that is what you want.

Do you only want the Day of Week or the Day of Week plus the Date shown in
the list.

Name of the worksheet and the cell address where you want to start the list.

Is the value to be copied always from the same cell address? If so, name of
the worksheet and cell address. If not, I will need some info on the setout
of the sheet so the macro can locate it. For example always at the bottom of
a list commencing at cell D1 or always the last cell in column D.


Regards,

OssieMac

 

[Matt]

Sounds like this is best done using a macro? If so, then yes, help
with that macro would be appreciated. I do know how to input the
macro at workbook level. It can calculate every day of the week or
just weekdays, either is fine, whatever makes the programming
simpler. It would also be good to show the date along with the day.
Finally the worksheet will be called Daily Tracking and the list can
start in A15. Thank you for your help!

Matt

 

[Guest]

Hi again Matt,

Macro below. You didn’t tell me where to copy the daily total from so I just
dummied a total for testing purposes. Can you fix that part or if not, can
you give me some info as per my previous posting.

You will have to initialize the first date at cell A15. (Or any cell in
column A so long as it does not have any data in cells below it otherwise the
macro will fail). Just put yesterdays date in. The macro needs this to run.

You will see that it always appends today’s date to the bottom of the list
and then tomorrow it will enter the total against the existing date. That way
I was able to use the date at the bottom of the list to determine if the
macro has been run. Also it will ignore any days that the workbook is not
opened.

I don’t know what part of the world you are in so you may have to edit the
date format. In case you are not fully familiar with custom formatting dates,
the format I have used of “ddd dd mmm yyyy†will display a date as Wed 19 Sep
2007 which achieves the day of the week and the date together.



Private Sub Workbook_open()

Dim dateToday As Date
Dim rngLastDate As Range
Dim lastDayTot As Variant

dateToday = Date

'Following line needs to be edited to pick
'up the total from your worksheet
lastDayTot = 10

With Sheets("Daily Tracking")
Set rngLastDate = .Cells(Rows.Count, 1).End(xlUp)
If rngLastDate.Value <> dateToday Then
rngLastDate.Offset(0, 1) = lastDayTot
rngLastDate.Offset(1, 0) = dateToday

'Edit date format to your requirements
.Columns("A:A").NumberFormat _
= "ddd dd mmm yyyy"
.Columns("A:A").Columns.AutoFit
End If
End With

End Sub

Feel free to get back to me if you have any problems with it.

Regards,

OssieMac

 

[Guest]

One more thing. Copy and paste the macro into ThisWorkbook.

Regards,

OssieMac