Automatically adding a new row with numbering on hitting 'enter' button

[darejack2001]

In the above image i have highlighted a cell, i have tried to customize this sheet by help of this forum to my use and i am stuck up with a new thing. Right now i have to manually insert a row into the sheet. I want to customize the sheet in such a way that when i type in data on the last cell and hit 'enter' key it should automatically create a new row and also increment the numbering on the left most column. Like in my case when i type in H17 cell and hit 'enter' key it should automatically create a new row and should update the below row's "sr no" field as +1, i.e. 13...!!

Thank you for your help guys...!! ​

 

[Gary's Student]

Put the following worksheet event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim H As Range
Set H = Range("H:H")
If Intersect(Target, H) Is Nothing Then Exit Sub
Application.EnableEvents = False
roow = Target.Row
Cells(roow + 1, 1).Value = Cells(roow, 1).Value + 1
Cells(roow + 1, 2).Select
Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window​

If you have any concerns, first try it on a trial worksheet.
If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window​

To learn more about macros in general, see:
Getting Started with Macros and User Defined Functions
To learn more about Event Macros (worksheet code), see:
Event Macros, Worksheet Events and Workbook Events

 

[darejack2001]

Thank you for your prompt reply..the code works but as in snapshot we could see that after 14th cell i have merged cells for "comments field". Now when at end of 13th row cell when i hit enter, it puts "sr no" as 15 in the comments field. Instead i want to shift those fields downwards and insert a new field without disturbing the below footer fields ...!!

Thank you..

 

[Gary's Student]

So if you enter a value in column H, you want the whole table to "Grow" downwards??

 

[darejack2001]

Right..i want the whole table to shift downwards once i hit 'enter' in my last cell of H column...!!

 

[Gary's Student]

Remove the old macro and replace it with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim H As Range
Set H = Range("H:H")
If Intersect(Target, H) Is Nothing Then Exit Sub
Application.EnableEvents = False
roow = Target.Row
Cells(roow + 1, 1).Value = Cells(roow, 1).Value + 1
Cells(roow + 1, 2).Select
Rows(Target.Row + 2).Insert
Application.EnableEvents = True
End Sub

 

[darejack2001]

Sorry for the late reply, this code seems to be doing the trick, it was correctly adding an extra row and adding the number to the first cell but it did not copy the formatting. Like it did not copy the table borders alongwith it...!!

Thanks..

 

[Gary's Student]

you are correct!

 

[darejack2001]

Actually i wanted it to copy the table formatting(borders) too..so that i don't need to manually draw a table for it..would it be possible to do the same...??