Is there a function or macro to update calendar in a way tha it runs through
the whole row automatially upon a click of a button, I need the different
months to appear on different worksheets. Dragging through the cells every
end of the month is too time-consuming.

e.g. 1st row 28 oct 07 29 oct 07 30 oct 07
2nd row Sunday Monday Tuesday

Will the calendar control object in Excel help in that?

Thanks in advance

Hi Ron,

If you insert a date in Sheet1 cell A1 (say 1 Jan 2007) then in B1 you
insert the formula =A1+1 and the copy that formula across then each time you
update A1 the the rest automatically update across the worksheet.

In cell B2 (below the dates) enter =A1 and copy it across to the end of the
dates in row 1 and then format the row to "ddd". While it is actually a date,
it will only display the day of the week and it will all update when you
change cell A1.

You say that you have different months on different worksheets. If you have
entered the date as above in cell A1 of Sheet1 (That is 1 Jan 2007) then in
cell A1 of Sheet2 enter the following formula:-

=EDATE(Sheet1!A1,1)

The formula returns the date 1 month after the date in Sheet1!A1 which will
be 1 Feb 2007. Do as for Sheet 1 and enter in B2 =A1+1 and copy across and
repeat row 2 as for Sheet 1. Do this throughout the workbook and then all you
have to do is change the date in cell A1 of Sheet1 and all the dates change
with it.

Regards,

OssieMac




"ron" wrote:

Is there a function or macro to update calendar in a way tha it runs through
the whole row automatially upon a click of a button, I need the different
months to appear on different worksheets. Dragging through the cells every
end of the month is too time-consuming.

e.g. 1st row 28 oct 07 29 oct 07 30 oct 07
2nd row Sunday Monday Tuesday

Will the calendar control object in Excel help in that?

Thanks in advance

Hi again,

Formatting date to "ddd" returns the abbreviation Mon Tue etc.

Foramtting as "dddd" returns the days as Monday Tuesday etc.

Regards,

OssieMac

"ron" wrote:

Is there a function or macro to update calendar in a way tha it runs through
the whole row automatially upon a click of a button, I need the different
months to appear on different worksheets. Dragging through the cells every
end of the month is too time-consuming.

e.g. 1st row 28 oct 07 29 oct 07 30 oct 07
2nd row Sunday Monday Tuesday

Will the calendar control object in Excel help in that?

Thanks in advance