Cell to show last day of month

[dolphinv4]

Hi,

I've created a macro that automatically copies from an
existing worksheet and insert a new sheet with the copied
data.

For a particular cell in the new sheet, I'd like the new
sheet to show the date of the last day of a subsequent
month, ie, in the existing sheet, the cell shows 30/4/04.
In the newly inserted sheet, I'd like it to show 31/5/04
on running the macro.

What code should I include in my macro for this to happen?

Thanks!
Val

 

[Earl Kiosterud]

Val,

I couldn't find a handy end-of-month function in VBA, and Eomonth isn't
supported in Application.WorksheetFunction. So I cheated. Put this in a
cell in your existing sheet:
=EOMONTH(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),0)
It calculates the end of the following month. You can hide the row or
column it's in. Set your new sheet to this date.

 

[JON JON]

Hello,

Assume your date is in A1, add this code

With Range("A1")
.Value = DateSerial(Year(.Value), Month(.Value) + 2, 0)
End With

Regards,

Jon-jon