Trouble Copying To Newly Inserted Row

[AcornNut]

I am working with 2 sheets. Sheet1 is an inventory sheet (name of sheet is "Location") and Sheet2 is an Order Form (Name of sheet is "Supply Usage Form").

"LOCATION"
A B&C (combined) D E F G(week1) H(week2) I(week3) J(week4) K(week 5) L(Total missing) M(total expiring)
Order Code Item Description Expiration Qty. Expiring Qty. Required Qty. In Stock Qty. In Stock Qty. In Stock Qty. In Stock Qty. In Stock
row
9
10
11
12
13
etc.

"SUPPLY USAGE FORM"
A B & C (comb.) D&E (comb.) F&G(comb.) H I J&K(comb.) L M&N(comb.)
No Data Order Code Item Description BLANK Qty. Used Qty. Expiring Qty. Damaged New Item Date Received
row
24
25
26
...
53

54 is a gap/spacer
55 and after has information that must remain after the above data.

I am trying to click a command button in the order form sheet and have the code look at the inventory ("LOCATION") sheet row by row for any data in columns L an M (these columns will display only when an item is missing or expiring)., then copy the order code and item description in the next blank row on the order form ("Supply Usage Form") along with the corresponding data from column L (and put it in column H of the order form) and from column M (and put it in column I of the order form).

This works very well as long as there are blank spaces, but row 53 is my last row. Any data that the program tries to insert after that messes up the information below row 54 (spacer). I need this information below row 54 to stay below the rest of the data. I've tried to insert a new row if the program shows there's data in row 53, and then proceed as described above. The problem is that the exact same copy and paste code that works so well initially, fails whenever I add the Row.insert line, regardless of where in the code process I place it.

I've copied my code below so hopefully you can get an idea of what I'm trying to accomplish. Any help would be GREATLY appreciated.

Sub fillorder1()


Dim finalrow As Integer
Dim i As Integer
Dim ExpDate As Date


Dim lastRow As Long



Application.ScreenUpdating = False

lastRow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
finalrow = Sheets("Location").Range("B9").End(xlDown).Row
ExpDate = Date + 30

If lastRow >= 53 Then
Rows(lastRow + 1).Insert
End If




For i = 9 To finalrow
If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") And (lastRow >= 53) Then
Rows(lastRow + 1).Insert
Rows(lastRow).Copy
Rows(lastRow + 1).PasteSpecial xlPasteFormats


Sheets("Location").Cells(i, 1).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 12).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 13).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
End If


If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") And (lastRow >= 53) Then
Rows(lastRow + 1).Insert
Rows(lastRow).Copy
Rows(lastRow + 1).PasteSpecial xlPasteFormats


Sheets("Location").Cells(i, 1).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 12).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
End If


If (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") And (lastRow >= 53) Then
Rows(lastRow + 1).Insert
Rows(lastRow).Copy
Rows(lastRow + 1).PasteSpecial xlPasteFormats


Sheets("Location").Cells(i, 1).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 13).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
End If


If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") And (lastRow <> 53) Then
Sheets("Location").Cells(i, 1).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 12).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 13).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
End If


If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") And (lastRow <> 53)Then
Sheets("Location").Cells(i, 1).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 12).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 13).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
End If


If (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") And (lastRow <> 53) Then
Sheets("Location").Cells(i, 1).Copy '
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 13).Copy
Sheets("Supply Usage Form").Range("B22").End(xlDown).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
End If
Next i


Application.ScreenUpdating = True
End Sub