Automatic new rows below new data

[DXL XLR8]

What I am basically trying to do is if a particular cell is entered with new data, then the macro will automatically run and create a new row below that cell, but with all the formulas still being copied down. Then repeats itself with the row that is just created where if data was entered in that same particular cell, a new row is copied down.

For example.

A ...........B ...........C ..........D
1 ......Testing 1 ......3 .......=A1+B1
2 ......Testing 2 ......3 .......=A2+B2
............^
............(New row if data entered in A2)
3 .......(Blank) .......3 ........=A3+B3

Then the process repeats itself.

Thanks in advance!

 

[wrightyrx7]

Please try the below on a COPY of your workbook.

Right click your worksheet and click 'View Code' then paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
        Target.Offset(1).EntireRow.Insert shift:=xlShiftDown
        Target.Offset(, 2).Copy Destination:=Target.Offset(1, 2)
    Application.EnableEvents = True
End Sub

It works if a value is changed in Column A
Hopefully it does as you want.

 

[DXL XLR8]

Please try the below on a COPY of your workbook.

Right click your worksheet and click 'View Code' then paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
        Target.Offset(1).EntireRow.Insert shift:=xlShiftDown
        Target.Offset(, 2).Copy Destination:=Target.Offset(1, 2)
    Application.EnableEvents = True
End Sub

It works if a value is changed in Column A
Hopefully it does as you want.

Where do I put the cell i want it to check to see if it's empty? I also want to integrate this copy row into it. Basically what it does is that it copies column a-c, skips d for data entry, then copy formulas in cells e-j in a particular row.

Sub Insert_Row()
R = ActiveCell.Row
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
Range("A" & R & ":C" & R).Copy Destination:=Range("A" & R + 1 & ":C" & R + 1)
Range("E" & R & ":J" & R).Copy Destination:=Range("E" & R + 1 & ":J" & R + 1)
End Sub


So basically if there is data in column D, it would copy everything above it expect column D so the user can enter new data. And once that new data is created, then it creates a new row and so on.

Hope this makes sense.

Thanks,