Solved Insert a row above and copy two cells from below

September 21, 2012 at 06:39:58
Specs: Windows XP
I insert a row everytime a customer changes and I now need to copy the data in cells A and B from the row below to the inserted row in cells A and B. I thought about coping the whole row and then delete all the data except for cell data in A and B.
I have the following code that works to insert the new row but can't get the copy to work:
Dim eRow As Long
For eRow = Cells(Cells.Rows.Count, "E").End(xlUp).Row To 2 Step -1
If Cells(eRow, "E") <> Cells(eRow - 1, "E") Then
Rows(eRow).EntireRow.Insert
Next eRow



See More: Insert a row above and copy two cells from below

Report •


#1
September 21, 2012 at 07:06:35
✔ Best Answer
First, a posting tip:
Before posting code in this forum, please click on the blue line at the end of this post and read this instructions found via that link.

Second, your code throws up an error because you are missing an End If.

I assume your code looks more like this:

Sub InsertRow()
Dim eRow As Long
  For eRow = Cells(Cells.Rows.Count, "E").End(xlUp).Row To 2 Step -1
    If Cells(eRow, "E") <> Cells(eRow - 1, "E") Then
      Rows(eRow).EntireRow.Insert
    End If
  Next eRow
End Sub

As for your question, try this:

Sub InsertRow()
Dim eRow As Long
  For eRow = Cells(Cells.Rows.Count, "E").End(xlUp).Row To 2 Step -1
    If Cells(eRow, "E") <> Cells(eRow - 1, "E") Then
      Rows(eRow).EntireRow.Insert
      Range(Cells(eRow + 1, 1), Cells(eRow + 1, 2)).Copy _
        Destination:=Cells(eRow, 1)
    End If
  Next eRow
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 21, 2012 at 07:23:39
That worked perfectly. I did read the post instructions, thank you for the tip.

Report •
Related Solutions


Ask Question