Automatically inserting row after enter, but range will change.

[Maverickone23]

Hello,
I'm having a problem trying to develop a code that will enable me to automatically insert a row as enter is pressed. Sounds easy, problem is this needs to happen throughout the excel sheet. There are titles to drawing packages in the sheet, two blank spaces between each title. As drawings are completed they will be placed under their appropriate title. When you press enter after writing out the drawing name, I want a new blank row to appear under the drawing. I can do this for the first drawing package, but since i have to reference the range where I want the blank row to be inserted it won't work for the 2nd thru 47th package. I need this to occur for every package, independently as enter is pressed. Example (Package 1 title is at row 3, Package 2 is at row 6, Package 3 is at row 9. When a drawing is added into the blank row below Package 1 title and enter is pressed, Package 2 drops to row 7 and Package 3 drops to row 10. When a drawing is added to package 2 and enter is pressed, package 3 will drop to row 11.) As you can see, the range will change for every package below Package 1 as drawings are entered. I thought perhaps writing code that will simply say there is to always be 2 blank rows above each title and then designate the titles. Can't figure that one out.

Thanks

 

[pbornemeier]

If Package Titles and Drawing Names are both in column A then put this code in the codepage of the worksheet of with that data and enter "Auto Insert" in J1 (could be anything, but this is descriptive).

Private Sub Worksheet_Change(ByVal Target As Range)
    DoEvents 'So this code can be interrupted with Break
    If Len(Range("J1").Value) > 0 Then  'Check so this code will be bypassed if J1 is empty
        If Len(Target.Cells(1, 1).Offset(2, 0).Value) <> 0 Then
            Application.EnableEvents = False 'so the insert does not trigger this code
            Target.Offset(1, 0).Cells(1, 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
            Application.EnableEvents = True
        End If
    End If
End Sub

There are several safety features built in to this code:
1) Will not insert rows unless there is at least one character in cell J1 on the worksheet (change to the cell of your choice).
2) DoEvents will allow interruption of the code if it gets in a loop.
3) Application.EnableEvents = False / True pair will prevent code from triggering itself.

If that does not work, consider the following questions:

Are drawing package titles and drawing names all in column A? or Are drawing package titles in A and drawing names in column B?
Is there some feature in the title row that will identify it as a title? (Perhaps a colon in that row). Otherwise when Enter is pressed for those rows two lines
So your example started with a new worksheet
.
.
Title1
.
.
Title 2
.
.
Title 3

and the first drawing for package 1 is added in row 4, just below the package 1 title. When enter is pressed the result should be:
.
.
Title1
Drawing 1 for Title 1
.
.
Title 2
.
.
Title 3

What should happen if a drawing title is inserted just above the next package name?