vba - Excel Macro - Copy Row -> Insert Row under -> Paste data

Question

I have a code that runs through column E(Column C in example below) and searches for any change in data. It then inserts a blank row under that change and loops through the set of data (200-500 rows long). I am looking for a way to modify/add to the code a "copy and paste" feature of the last row of data, before the change, into the newly inserted row.

Before:

Column A	Column B	Column C	Column E
1	2	Sally	5
1	2	Sally	6
1	2	Sally	2
1	2	Chase	1
1	2	Chase	4
1	2	Ben	9

question from:https://stackoverflow.com/questions/65890535/excel-macro-copy-row-insert-row-under-paste-data

Answer 1

Edited::

Try the updated code, please:

Sub testInsertRowCopyBefore()
  Dim sh As Worksheet, lastRow As Long, i As Long
  
  Set sh = ActiveSheet
  lastRow = sh.Range("A" & Rows.count).End(xlUp).row
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
   For i = lastRow + 1 To 3 Step -1
    If sh.Range("C" & i).Value <> sh.Range("C" & i - 1).Value Then
        sh.Range("C" & i).EntireRow.Insert xlUp
        sh.Range("B" & i & ":C" & i).Value = sh.Range("B" & i - 1 & ":C" & i - 1).Value
    End If
   Next i
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  MsgBox "Ready..."
End Sub

The above code assumes that "Column A", "Column B" and "Column C" are the headers, which stay on the sheet first row.

Please, test it and send some feedback