Move Row to another Sheet if Date is In a Colum

[ryanf88]

Hello

I have an excel worksheet with K Columns with info starting on Row 5 to 30
I would like to move a row to the second sheet if a date has been put under Column I which is called "Closed Date"
It will delete the row from Sheet1 and put it to the top row in Sheet2. For example the info from Sheet1 say Row 6 will be put into Sheet2 on Row 5 with the rest of the info moving down on sheet2 to allow for the inserted line to go in.

Is this even possible?

If not is it easier to delete it from Sheet1 and insert it at the next available row on Sheet2.


I am using Excel 2007 and am not very familiar with recording a macro so that wont work.


Thank-you for anyone who responds.

 

[patel45]

please upload a sample file on a share site then paste the link here

 

[ryanf88]

please upload a sample file on a share site then paste the link here

Here is a link to the file:

http://www.filedropper.com/pcrsummarysheettest


Thank-you

 

[patel45]

Sub a()
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
LR1 = sh1.Cells(Rows.Count, "B").End(xlUp).Row
drow = 5
For j = 5 To LR1
  If sh1.Range("I" & j) <> "" Then
    sh1.Rows(j).Copy sh2.Range("A" & drow)
    sh1.Rows(j).Delete
    j = j - 1
    drow = drow + 1
  End If
Next
End Sub

 

[ryanf88]

Sub a()
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
LR1 = sh1.Cells(Rows.Count, "B").End(xlUp).Row
drow = 5
For j = 5 To LR1
  If sh1.Range("I" & j) <> "" Then
    sh1.Rows(j).Copy sh2.Range("A" & drow)
    sh1.Rows(j).Delete
    j = j - 1
    drow = drow + 1
  End If
Next
End Sub

Thank-you so much for the code.
Is there a way to make this run without pressing the play button each time in excel? (Not sure if it is a setting or part of code)