I have a row with months in the format Jan-14 (01-01-2014), Feb-14 (01-01-2014) and so on, starting from column 3. I want the row to automatically update such that it contains the months from Jan-14 to the present month + two months. That is, since today's date is 25-08-2014, the row should contain the months Jan-14,...., Aug-14 (01-08-2014), Sept-14 (01-09-2014) and Oct-14 (01-10-2014). I have tried to do the following code but it is currently not working. Any suggestions how to make this work?
Sub Updatemonths()
Dim col As Long
Dim lc As Variant
lc = Worksheets("Summary").Cells(4, Worksheets("Summary").Columns.Count).End(xlToLeft).Column
Dim StartD As Date, EndD As Date
StartD = Worksheets("Summary").Cells(4, lc)
EndD = Date
For col = 1 To (EndD - StartD)
If IsDate(Cells(4, lc + col)) Then
Worksheets("Summary").Cells(4, lc + col).Value = DateSerial(Year(Cells(4, StartD + col)), Month(Cells(4, lc + col)), 1)
End If
Next col
End Sub
Best regards
Christian Ulrich
Bookmarks