How to increment a list of dates

B

Bill

I have an Excel spreadsheet with a column of successive dates and I
want to devise a macro to increment the month by one, at the end of
each month so that I can use the sheet again. Is there as easy way to
"one-button" this?
 
D

Don Guillett

why not just use a month number in cell e1 (or another and use)
=DATE(2004,$E$1,1)
 
D

David McRitchie

Hi Bill,
Some months have 28, 29, 30, 31 days
Some calendars only show weekdays, or other certain days.
Are you trying to retain any information besides the days 1-31.

It might just be easier to generate a new sheet from scratch with your
macro. If you are talking about one button you are talking about a
macro. No error check for preexisting sheetname to be generated.

Sub MakeCalendarSheet()
'David McRitchie, 2004-07-05, .excel
Dim fromDate As Long, todate As Long
Dim i As Long, j As Long
fromDate = DateSerial(Year(Now), Month(Now) + 1, 1)
todate = DateSerial(Year(Now), Month(Now) + 2, 0)
'Create New Sheet
Sheets.Add After:=Sheets(Sheets.Count) '-- place at end
'Rename current Sheet
ActiveSheet.Name = "D" & Format(fromDate, "yyyy_mm")
ActiveSheet.Range("A:A").HorizontalAlignment = xlLeft
Range("A:A").NumberFormat = "yyyy-mm-dd ddd"
Range("A1").Value = "'Date"
j = 1
For i = fromDate To todate
j = j + 1
Cells(j, 1) = i
Next i
Columns("A:A").EntireColumn.AutoFit
End Sub

Instructions to install and use a macro in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top