Insert new rows

[Fellow]

I posted this earlier, but it didn't appear (my apologies if this is the
second time).

My table looks like this:

A B C D E
F G
1 Organisation Adress Contact Org. type Linkage Outcome
2 Employer
3 Industry
4 Schools
5 Referral
6 Employment
7 Groups
8 Training
9 Community
10 Media
11 Other

Each of the "A" culumn is a heading category with one row each. I wanted to
be able to generate a new row when data is entered into each category (eg
when new data is added to B2 a new line will automatically appear at 3 to
allow room for new data for the employer category). A new line would insert
for each of the column A categories in the same way.

Thankyou for your time and help with this, much appreciated.

 

[Jacob Skaria]

You need to use a VBA solution to do this. Select the sheet tab which you
want to work with. Right click the sheet tab and click on 'View Code'. This
will launch VBE. Paste the below code to the right blank portion. Get back to
to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:G20")) Is Nothing Then
If Target <> "" Then
Application.EnableEvents = False
If Range("A" & Target.Row + 1) <> "" Then Rows(Target.Row + 1).Insert
Application.EnableEvents = True
End If
End If
End Sub

If this post helps click Yes

 

[joel]

You could do this with a macro, but I don't think it is a good idea t
automatically add rows when data is entered into column B because row
will get added when you have to change data in colun B.

I would either just highlight the entire row by clicking on the ro
number and then right click. there is an option Insert which will add
new row.

If I would do this with a macro I would add a control button that yo
would press to add the row rather than to have it automatically don
when you enter into column B

 

[Fellow]

Thanks Jacob - perfect!
Is there anyway for the blank boxes to contain the word (new)?
Cheers,
Fellow

 

[Fellow]

thanks Joel.