automatically fill cell with end of date monthly

hi,
I have a monthly reports generated by the button to create a new sheet,I want the new sheet A1 in cell filled with the date of the end of the month, Can the code that can automatically create entries, the sheet name is " jan10" ( end of date is jan 31), "feb10" (feb28).etc

thanks in advance
herukuncahyono

You don't stipulate how you're creating the new sheet but let's assume something along the lines of:

With Sheets.Add
    .Name = "Feb10" 'presumably this is a variable of some sort
    .Cells(1, "A") = DateAdd("m", 1, "01-" & Left(.Name, 3) & "-" & Right(.Name, 2)) - 1
End With

above assumes a consistent naming pattern of mmmyy

Without knowing how you're controlling data entry on the UserForm (or indeed if) - perhaps:

Sheets("form").Copy After:=Sheets(Sheets.Count)
With ActiveSheet
    .Name = UserForm1.TextBox1.Text
    .Cells(1, "A") = DateAdd("m", 1, "01-" & Left(.Name, 3) & "-" & Right(.Name, 2)) - 1
End With

though as implied you will need to validate the TextBox string first - so as to ensure consistency.

...and the problem is what exactly ?

I was simply making the point that unless you control the entry text the code could debug - eg I type in "apple" and hit OK.