This subroutine will increment a month in a provided cell.

Sub AddMonth()
Dim sDate As String, dDate As Date

setUp

Sheets("Month").Select
Range("A3").Select
If IsNull(Sheets("Month").Range("A3").Select) Or Sheets("Month").Range("A3").Value = "" Then
Selection.End(xlToRight).Select
End If
sDate = ActiveCell.Value
dDate = CDate(sDate)

If Day(dDate) 1 Then
dDate = DateAdd("D", -Day(dDate) + 1, dDate)
End If
dDate = DateAdd("M", 1, dDate)
Sheets("Month").Range("G1:H1").Value = Format(dDate, "mmmm-yyyy")
fillMonth dDate

closeOut
End Sub

Then you can update a given month

Sub UpdateMonth()
Dim sDate As String, dDate As Date
setUp
Sheets("Month").Select
Range("A3").Select
If IsNull(Range("A3").Select) Or Range("A3").Value = "" Then
Selection.End(xlToRight).Select
End If
sDate = Sheets("Month").Range("G1").Value
dDate = CDate(sDate)

fillMonth dDate
closeOut
End Sub

Or decrement a month

Sub SubMonth()
setUp
Dim sDate As String, dDate As Date

Sheets("Month").Select
Range("A3").Select
If IsNull(Range("A3").Select) Or Range("A3").Value = "" Then
Selection.End(xlToRight).Select
End If
sDate = ActiveCell.Value
dDate = CDate(sDate)

If Day(dDate) 1 Then
dDate = DateAdd("D", -Day(dDate) + 1, dDate)
End If
dDate = DateAdd("M", -1, dDate)
Range("G1:H1").Value = Format(dDate, "mmmm-yyyy")
fillMonth dDate
closeOut
End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s