Date for excel workbook

[aacod]

I have an excel workbook with 31 sheets for the month og March, each representing a day in a month. I would like an excel formula which would put the date automatically on each sheet for that particular day, e.g. 1 March, 09 on sheet 1 and 2 March on sheet 2 etc. for the whole month. Thanks

 

[xenou]

This has almost no protection against crashes or errors - i.e., if we run out of sheets or something.

Sub foo()
Dim ws As Worksheet
Dim myDate As Date

Set ws = ActiveSheet
myDate = DateSerial(2009, 3, 1)

On Error Resume Next
x = 1
Do While myDate <= DateSerial(2009, 3, 31) AND x <= 31
    ws.Name = Format(myDate, "mmmm d, yyyy")
    myDate = myDate + 1
    Set ws = ws.Next
    x = x + 1
Loop

End Sub

(Names tabs with days of month in march)

Alex

 

[aacod]

This works fine , but would appreciate if it works in the following format 1 Mar, 2 Mar, 3 Mar etc .

Thanks

 

[aacod]

I got the code for format day, month. thanks

 

[Joseph.Marro]

What are your sheet names?

 

[aacod]

Sheet Names are 1 Mar, 2 Mar till 31 Mar for the whole month of March.

 

[Joseph.Marro]

Ahh, mis-understood the question I thought you wanted the day of the current month based on sheet position to be displayed in cell.