Insert Row below current row when...

[russmeister]

I want to insert a new row with the existing formulas that I have in the current row. I want this row to be below the current row and I want it to insert when data in column A is changed from empty to a selection from a dropdown list. Can anyone help with this?

 

[Jeffrey Lopez]

Welcome to the Mr. Excel Message Board!

So first this is going to need to be a Worksheet_Change event, so it will need to be put in the Worksheet code. (Right Click on the Sheet Name > View Code > Paste it there)

The code you might want to try would be something like this:

First we need to define what range to look for the changes in, in this example I am just using A:A. You can change this to more specific range if you like, something like: A1:A25 or whatever, but as of now it will look for any change in the column A.

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

Now we need to tell it what to look for and what to do with it. I am using a simple IF the value is great than 0 (which is basically everything).

If Target.Value > 0 Then

Next we need to tell it what do when everything is true:

Target.Offset(1, 0).EntireRow.Insert shift:=xlDown
Range(Target.Offset(0, 1), Target.Offset(0, 10)).Copy Destination:=Target.Offset(1, 1)
[CODE]

Basically, we are saying, Where it finds the change, 1 row down (Offset(1,0)) insert a row, then copy the range B2:K2 or (Target.Offset(0, 1), Target.Offset(0, 10)) and paste it in the row we just inserted.  If you need more than 10 columns just change the Target.Offset(0,10) to the total number of columns you want to copy...

And that is it... I hope that helps, and as always, [COLOR=#2f4f4f][B]please back up your excel workbook before running this macro![/B][/COLOR]

[CODE]
Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ErrorHandler:
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub


If Target.Value > 0 Then


Target.Offset(1, 0).EntireRow.Insert shift:=xlDown


' Change the Target.Offset(0,10) to however many columns there are in your tabel...
Range(Target.Offset(0, 1), Target.Offset(0, 10)).Copy Destination:=Target.Offset(1, 1)


End If
ErrorHandler:
End Sub