Excel Macro to insert row based on data

Sub undo()
Range("D1:H1").EntireColumn.Delete
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("a1")
Application.CutCopyMode = False
End Sub

Sub test()
Dim r As Range, vendor As Range, cvendor As Range
Dim j As Long, cfind As Range, dest As Range, rdest As Range
Application.ScreenUpdating = False
undo
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
r.AdvancedFilter xlFilterCopy, , Range("d1"), True
Set vendor = Range(Range("D2"), Range("D2").End(xlDown))
For Each cvendor In vendor
j = WorksheetFunction.CountIf(r, cvendor.Value)
If j <> 12 Then
Set cfind = r.Find(what:=cvendor.Value, lookat:=xlWhole)
Set dest = Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
Range(cfind, cfind.Offset(0, 1)).Copy Range(dest, dest.Offset(11, 0))
dest.Offset(0, 2) = 1
Set rdest = Range(dest.Offset(0, 2), dest.Offset(11, 2))
'MsgBox rdest.Address
rdest.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
       Step:=1, Stop:=12, Trend:=False
Else
Set dest = Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
Range(cfind, cfind.Offset(11, 2)).Copy dest
End If
Next cvendor
Range("D1").EntireColumn.Cells.Clear
MsgBox "macro over"
Application.ScreenUpdating = True
End Sub