Inserting a row after a specific date

[jake424]

Hello MrExcel,

I am looking for help creating a Macros in Excel. Every year I have to update rates for the next year. I have about 1200+ rows of data and about 100 items. Each item has "Effective Dates" in column B starting from 1/1/2002, then 1/1/2003 all the way up to 1/1/2014 so every 12 rows or so the an item number ends with the effective date 1/1/2013.

What I need is to insert a row after each effective date of 1/1/2013 and copy the information in columns D:J down to the next row. Does this make sense?

In essence, I have

Carrier Effective Cancelled Item Subitem Carrier Item
XYZ 1/1/2011 1/1/2012 2 1532 9.24
XYZ 1/1/2012 1/1/2013 2 1532 9.24
XYZ 1/1/2013 / / 2 1532 9.24

I would like to run something that would search for 1/1/2013 in column "B" and insert a new row with the information in columns A, D, E, F, etc. like so:


Carrier Effective Cancelled Item Subitem Carrier Item
XYZ 2 1532 9.24

Please help.


Thanks!

 

[XLfriend777]

Hi jake424 - Give this a shot and let me know if it works for you.

Happy excelling,

goesr

Sub jake424_findDate()
Dim i As Integer 'Used to increment rows
i = 1
While Cells(i, 2) <> ""
Cells(i, 2).Select
    If Cells(i, 2) = 41275 Then 'Numerical value of 1/1/2013
        Cells(i, 2).Select
        ActiveCell.Offset(1).EntireRow.Insert
        
        '========= START Copy Data
            Range(Cells(i, 4), Cells(i, 10)).Copy
            Range(Cells(i + 1, 4), Cells(i + 1, 10)).Select
            ActiveSheet.Paste
            
        'Also copy contents of Column A down one row (i + 1)
            Cells(i, 1).Copy
            Cells(i + 1, 1).Select
            ActiveSheet.Paste
        '========= END   Copy Data
        
        i = i + 1 'Adds to rows since you added a row
    End If
i = i + 1 'Increments through rows while column 2 is not empty
Wend
End Sub