insert rows automatically

[sbrown64]

HiI have created a table & in column B I enter a number (i.e 3) is there a way to automatically add 2 rows below the line, so if the number was 5, it would add 4 rows below?RegardsStephen​

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/13/2019  10:05:27 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And IsNumeric(Target.Value) Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

 

[sbrown64]

Hi

i followed your instructions and when i put the number 3 in column B, nothing happened?

 

[My Aswer Is This]

Well it works for me. I test all my scripts.
Did you install the script as I explained.

And you entered a number like 3 in column B

And your workbook is Macro Enabled.

 

[sbrown64]

I di everything you mentioned

here is the link to the file

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://1drv.ms/x/s!Aq3WqOz73fYygYUnHWHhyScSho9udw?e=OCTc1u

Regards
Stephen
[/FONT]

 

[My Aswer Is This]

I never click on links or open files posted here:

We are talking about Column(2) of your worksheet.

 

[sbrown64]

yes, column B is where I put a number and when I tab across from it, nothing happens. So if I put 3 on line 50, I would want it to add two more lines below and then I can fill in the data and then on line 53 I may put the number 1, so not needing another line.

[TABLE="width: 802"]
<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>[TR]
[TD="align: right"]04/08/2019[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]BRISTOL CITY A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]HERNANDEZ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BAMFORD[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]HARRISON[/TD]
[/TR]
[TR]
[TD="align: right"]10/08/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]NOTTINGHAM FOREST H[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]HERNANDEZ[/TD]
[/TR]
[TR]
[TD="align: right"]17/08/2019[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]WIGAN A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BAMFORD[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BAMFORD[/TD]
[/TR]
[TR]
[TD="align: right"]21/08/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]BRENTFORD H[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NKETIAH[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2019[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]STOKE CITY A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DALLAS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ALIOSKI[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BAMFORD[/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]SWANSEA H[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]



[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 802"]
<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Did you get any error messages?

 

[My Aswer Is This]

I really do not know what to say.

If you enter 4 into Range("B4") for example there should be 3 rows inserted below Row(4)

This must be done manually not the result of a formula causing the change.

 

[sbrown64]

i saved your code to the worksheet page, not a mudule and when i go to the next line. i entered a date and then tabbed to column B and entered number 6 using the keyboard and nothing happened.