How can I

[Bryan De-Lara]

Could anyone please tell me how to delete values from a cell automatically
every time a work book is opened.
What I need to do is:-
On everyday from last year 2007 each cell could contain a value. In 2008
when we reach that same date, if 2007 has a value it has to be deleted, but
not before we reach that date.
i.e. 16 March 2007 would be deleted 16 March 2008. If a workbook hasn't been
opened for a week, then all the dates from the past week needs to be deleted
in one go.
I have a column with the days of the year (254), then columns with a value
in some cells going as far across as IL.
Can this be done?
As always, your help would be greatly appreciated.

Bryan.

 

[Pete_UK]

Bryan,

you would need a macro for this, but I'm not sure how it would
determine that the values you talk about referred to last year. From
an earlier post, you were talking about putting working dates in your
file for the whole year, so is this a different file? Do you have
working dates in this year up to the last time the file was opened,
and subsequent dates are for the previous year? If so, then you would
need the macro to change the dates for you - but how would it know
about Bank Holidays for the current year? Don't forget also that 17th
March 2007 (one year before today) was not a working day, so it would
not appear in your file when it is opened today.

Pete

 

[Bryan De-Lara]

A macro is what I thought would be suggested. Yes Pete you are right when
you mention 17th March 2007 was a working day but not 2008.
What I have is two years, every working day going down. Both years start on
Jan 2nd. To get the count done in blocks because they are rolling years, it
had to be done this way. If I had like I did at first, had each year on a
separate sheet then it would sometimes give a false reading if someone was
sick the last day in 2007 and the first day in 2008, when in fact it was
part of a block.
A marcro for what I am trying to accomplish sounds very complex. I can just
about record a macro on a button to take me to a predefined place in a
workbook. I've had no training in any application so I think writing a
macro, and I have looked at a few, will be beyond me. I'm pleased with what
I have now, it just means deleting has to be done manually. Where I am now
is down to people like yourself, who give their time and expertise freely
and you all need congratulating on your generosity.
Thanks also to Dude Ranch for your input also.

Bryan.




Bryan,

you would need a macro for this, but I'm not sure how it would
determine that the values you talk about referred to last year. From
an earlier post, you were talking about putting working dates in your
file for the whole year, so is this a different file? Do you have
working dates in this year up to the last time the file was opened,
and subsequent dates are for the previous year? If so, then you would
need the macro to change the dates for you - but how would it know
about Bank Holidays for the current year? Don't forget also that 17th
March 2007 (one year before today) was not a working day, so it would
not appear in your file when it is opened today.

Pete