Macro to copy rows and populate month/year based on start date? [Closed]

Report
Posts
1
Registration date
Wednesday October 26, 2016
Status
Member
Last seen
October 26, 2016
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
Hi all, I have a set of data where I need to create a row for every month through December 2016 based on a start date. For example, below is how the data looks now:

Rep name Start Date
Alice 10/1/2016
Dave 8/1/2016

And below are the rows I need to end up with (the date format changes as well, but my main concern is generating the rows in an automated way rather than copying/pasting):

Rep name Start Date
Alice October, 2016
Alice November, 2016
Alice December, 2016
Dave August, 2016
Dave September, 2016
Dave October, 2016
Dave November, 2016
Dave December, 2016

Thanks so much for any help you can offer!

1 reply

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi ekwacillin,

With the following code, the source data is taken from column A and B and the result is placed in columns C and D:
Sub RunMe()
Dim x, mMonth As Integer
For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    mMonth = Month(cell)
    x = 0
    Do
        Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = cell.Offset(0, -1)
        Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Format(DateAdd("m", x, cell), "mmmm") & ", " & Format(cell, "yyyy")
        mMonth = mMonth + 1
        x = x + 1
    Loop Until mMonth > 12 And x > 1
Next cell
End Sub

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!