i am maintaiining versions of my excel sheet. for this case i need to have 5
working weekdates as 5 columns... say column F2 to J2

I want to write within a macro to display dates in such a way that when im
from 3March to 7 MArch monday to friday it should show me mon to fri and when
i go to next week and when i open this old sheet it should not show the
current dates but it shud show last week dates..

In otehr words to maintain thsi versioning weekly, i need teh dates to be
static for taht week and when i open the new dates it shud show me
appropriate from mon to fri..
please help. I tried formulas,... but since this is an automation tool i
need to write within a macro

These formulas will work. I think it is bettter in formulas than as a macro

F2 : =IF(NOW()-DATEVALUE("3/3/08")5,DATEVALUE("3/3/08")5,"Monday")
G2 : =IF(NOW()-DATEVALUE("3/4/08")5,DATEVALUE("3/3/08")4,"Tuesday")
H2 : =IF(NOW()-DATEVALUE("3/5/08")5,DATEVALUE("3/3/08")3,"Wednesday")
I2 : =IF(NOW()-DATEVALUE("3/6/08")5,DATEVALUE("3/3/08")2,"Thursday")
J2 : =IF(NOW()-DATEVALUE("3/7/08")5,DATEVALUE("3/3/08")1,"Friday")

"dee" wrote:

i am maintaiining versions of my excel sheet. for this case i need to have 5
working weekdates as 5 columns... say column F2 to J2

I want to write within a macro to display dates in such a way that when im
from 3March to 7 MArch monday to friday it should show me mon to fri and when
i go to next week and when i open this old sheet it should not show the
current dates but it shud show last week dates..

In otehr words to maintain thsi versioning weekly, i need teh dates to be
static for taht week and when i open the new dates it shud show me
appropriate from mon to fri..
please help. I tried formulas,... but since this is an automation tool i
need to write within a macro

Try this worksheet event macro:

Private Sub Worksheet_Activate()
v = Day(Date)
If v < 3 Then Exit Sub
Set f = Range("F2")
If v = Day(f.Value) Then Exit Sub

For i = 0 To 4
f.Offset(0, i).Value = Date + i
Next
End Sub

Whenever the sheet is opened, the macro runs. If today is not Monday,
nothing happens. If the dates have already been refreshed this week, nothing
happens.

Otherwise the dates in F2 thru J2 get settup for the week.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm




--
Gary''s Student - gsnu200771


"dee" wrote:

i am maintaiining versions of my excel sheet. for this case i need to have 5
working weekdates as 5 columns... say column F2 to J2

I want to write within a macro to display dates in such a way that when im
from 3March to 7 MArch monday to friday it should show me mon to fri and when
i go to next week and when i open this old sheet it should not show the
current dates but it shud show last week dates..

In otehr words to maintain thsi versioning weekly, i need teh dates to be
static for taht week and when i open the new dates it shud show me
appropriate from mon to fri..
please help. I tried formulas,... but since this is an automation tool i
need to write within a macro

but when i copied and pasted this code inside the VBA and run it, nothing
appeared on my sheet . how can i help myself here

"Gary''s Student" wrote:

Try this worksheet event macro:

Private Sub Worksheet_Activate()
v = Day(Date)
If v < 3 Then Exit Sub
Set f = Range("F2")
If v = Day(f.Value) Then Exit Sub

For i = 0 To 4
f.Offset(0, i).Value = Date + i
Next
End Sub

Whenever the sheet is opened, the macro runs. If today is not Monday,
nothing happens. If the dates have already been refreshed this week, nothing
happens.

Otherwise the dates in F2 thru J2 get settup for the week.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm




--
Gary''s Student - gsnu200771


"dee" wrote:

i am maintaiining versions of my excel sheet. for this case i need to have 5
working weekdates as 5 columns... say column F2 to J2

I want to write within a macro to display dates in such a way that when im
from 3March to 7 MArch monday to friday it should show me mon to fri and when
i go to next week and when i open this old sheet it should not show the
current dates but it shud show last week dates..

In otehr words to maintain thsi versioning weekly, i need teh dates to be
static for taht week and when i open the new dates it shud show me
appropriate from mon to fri..
please help. I tried formulas,... but since this is an automation tool i
need to write within a macro