format new rows

[Fellow]

My table looks like this:

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

Jacob Skaria was able to help me 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] with the VBA solution:

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

This was most helpful.

However, I would also like to be able to keep entries with their categories
so they are not confused when i filter them (e.g. when new data is entered
into B2 and a new row appears I would like column A to also display the
category name in the new row at 3. for example A3 will now have the name
"employer" as well as A2. This would be replicated for each of the
categories.

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

 

[Jacob Skaria]

Hi again

Try the below modified one to suit your requirement

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) <> "" Then
Rows(Target.Row + 1).Insert
Range("A" & Target.Row + 1) = Range("A" & Target.Row)
End If
Application.EnableEvents = True
End If
End If
End Sub

 

[Fellow]

Once again, thankyou Jacob. You are right on the money.

Hi again

Try the below modified one to suit your requirement

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) <> "" Then
Rows(Target.Row + 1).Insert
Range("A" & Target.Row + 1) = Range("A" & Target.Row)
End If
Application.EnableEvents = True
End If
End If
End Sub

--
Jacob

My table looks like this:

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

Jacob Skaria was able to help me 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] with the VBA solution:

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

This was most helpful.

However, I would also like to be able to keep entries with their categories
so they are not confused when i filter them (e.g. when new data is entered
into B2 and a new row appears I would like column A to also display the
category name in the new row at 3. for example A3 will now have the name
"employer" as well as A2. This would be replicated for each of the
categories.

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