automatically fill cell with end of date monthly

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