Days in next month

[austinpcherry]

I would like to create a button to dynamically make a sheet for each day of the next month and rename it to the day.
I am having trouble obtaining an accurate number for the days in next month. I can't really move forward until this variable is defined. I am sure it is something simple that I am just overlooking.
I appreciate the help in advance.

Sub NewMonth()
Dim nextMonth As String
Dim daysInNextMonth As Double

nextMonth = Month(Now) + 1

Application.ScreenUpdating = False

Workbooks.Add
ActiveWorkbook.SaveAs "C:\Group Documents\TRUCK LOG\TRUCK LOG\ SERETEAN TRUCK LOG " & MonthName(nextMonth, True) & " " & Year(Now), xlOpenXMLWorkbookMacroEnabled

Application.DisplayAlerts = False
Worksheets("Sheet2").Delete
Worksheets("Sheet3").Delete
Application.DisplayAlerts = True

[COLOR=#ff0000]daysInNextMonth= ??????????[/COLOR]

Worksheets.Add Count:=daysInNextMonth - 1

'........... More to follow .........

Application.ScreenUpdating =True
End Sub

 

[dmt32]

Hi,
Try:

Dim NextMonth As Integer, DaysInNextMonth As Integer


NextMonth = Month(Now) + 1


DaysInNextMonth = Day(DateSerial(Year(Date), NextMonth + 1, 1) - 1)

Dave

 

[igold]

How about:

Dim DaysInNextMonth As Integer


DaysInNextMonth = Day(DateSerial(Year(Now), Month(Now) + 2, 0))

igold

 

[austinpcherry]

Just checked back. Thank you guys. I was still working on it. Anyone see any reason that this won't work?

daysInNextMonth = Day(CDate(Application.WorksheetFunction.EoMonth(nextMonth, 0)))

 

[igold]

Hi,

I poked around a bit and would seem that EoMonth does not play nice with VBA. I saw a lot of workarounds but I think that both dmt32 and myself gave you the best approach (imho).

igold

 

[austinpcherry]

Ok, thank you.

 

[ukmikeb]

Try :-

DaysInNextMonth = Day(Application.WorksheetFunction.EoMonth(INT(Now()), 1))

hth