Insert new rows

  • Thread starter Thread starter Fellow
  • Start date Start date
F

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.
 
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
 
Thanks Jacob - perfect!
Is there anyway for the blank boxes to contain the word (new)?
Cheers,
Fellow
 
Back
Top