Macro to insert new row when value changes with data from the cell above & autosum

[Mux99]

Hi all

I would like to insert a new row after the value changes in column C and in the new row copy the value from the cell above in column C and display the total in column D.

After running the macro it should look like the below

Thanks

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub Mux99()
   Dim i As Long
   Dim Rng As Range
   For i = 9 To 2 Step -1
      If Cells(i, 3) <> Cells(i - 1, 3) Then
         Rows(i).Insert
         Cells(i, 3) = Cells(i - 1, 3)
      End If
   Next i
   For Each Rng In Range("D:D").SpecialCells(xlConstants).Areas
      Rng.Offset(Rng.Count).Resize(1).Formula = "=sum(" & Rng.Address & ")"
   Next Rng
End Sub

 

[Mux99]

Hi & welcome to MrExcel
How about

Sub Mux99()
   Dim i As Long
   Dim Rng As Range
   For i = 9 To 2 Step -1
      If Cells(i, 3) <> Cells(i - 1, 3) Then
         Rows(i).Insert
         Cells(i, 3) = Cells(i - 1, 3)
      End If
   Next i
   For Each Rng In Range("D:D").SpecialCells(xlConstants).Areas
      Rng.Offset(Rng.Count).Resize(1).Formula = "=sum(" & Rng.Address & ")"
   Next Rng
End Sub

Thanks for your help. This added a new row and copied the data from row C but the total for row D didn't work. Also is it possible to insert a new row like the rest on the last row with data in it.

Run-time error 1004.
No Cells were found.

 

[Fluff]

What do you have in column D?

 

[Mux99]

What do you have in column D?

Column D is Col B minus Col A

=(B1-A1)
=(B2-A2)
=(B3-A3)

 

[Fluff]

Ok, how about

Sub Mux99()
   Dim i As Long
   Dim Rng As Range
   
   For i = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Cells(i, 3) <> Cells(i - 1, 3) Then Rows(i).Insert
   Next i
   For Each Rng In Range("D:D").SpecialCells(xlFormulas).Areas
      With Rng.Offset(Rng.Count)
         .Resize(1).Formula = "=sum(" & Rng.Address & ")"
         .Offset(, -1).Resize(1).Value = .Offset(-1, -1).Value
      End With
   Next Rng
End Sub

 

[Mux99]

Ok, how about

Sub Mux99()
   Dim i As Long
   Dim Rng As Range
   
   For i = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Cells(i, 3) <> Cells(i - 1, 3) Then Rows(i).Insert
   Next i
   For Each Rng In Range("D:D").SpecialCells(xlFormulas).Areas
      With Rng.Offset(Rng.Count)
         .Resize(1).Formula = "=sum(" & Rng.Address & ")"
         .Offset(, -1).Resize(1).Value = .Offset(-1, -1).Value
      End With
   Next Rng
End Sub

This one works perfectly. Thanks for the help

 

[Fluff]

You're welcome & thanks for the feedback