How to increment a list of dates

  • Thread starter Thread starter Bill
  • Start date Start date
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?
 
why not just use a month number in cell e1 (or another and use)
=DATE(2004,$E$1,1)
 
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
 
Back
Top