Cut/Paste Rows To New Woorkbook

[CEG]

I would like to create a macro that would search in column "N" and whenever it finds a date, it would cut that row out and open another workbook and paste it in row 4, shifting all rows down. Of course, I would like it to loop thru the whole workbook before it ends. Can anyone help?

Thanks,
CEG

 

[CEG]

Ok, I got this code to work somewhat...

Sub Merge_Data()
    Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim FinalRow As Long
    Dim i As Long
    Application.EnableEvents = False
 
    Set wb1 = Workbooks("Copy of BHEDC QT.xls")
    Set ws1 = wb1.Sheets("part number")
    Set wb2 = Workbooks.Open("C:\Folder\File.xls")  'Edit as needed
    Set ws2 = wb2.Sheets("part number") 'Edit as needed
 
    FinalRow = ws1.Cells(Rows.Count, 14).End(xlUp).Row
 
    For i = 4 To FinalRow
        'If IsDate(Cells(i, 14).Value) Then
        If ws1.Cells(i, 14).Value > 1 Then
            With ws1.Cells(i, 14).EntireRow
            .Value = .Value
            .Cut
            End With
            ws2.Rows(4).Insert Shift:=xlDown
 
        End If
    Next i
    Application.EnableEvents = True
End Sub

Now the problem I have, is that the row that I am cutting and pasting has formulas in some of the cells, so I thought the .Value = .Value would work, but it doesn't. I get #NAME in those cells.

Another problem, when it finds something in column "N" and cuts & pastes it, I would like it to delete the row that is now empty.