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

Question

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!

TrowaD · Answer

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

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

1 response

Similar discussions

Subscribe To Our Newsletter!