Add Specific number of rows based on cell value

[Utradeshow]

Hi All,

I have this code that adds rows based on the value of cell A2. I need two things. I would like the code to run after the value is entered in cell A2 vs having to press a add button. and also would like it to add rows starting from row 11 down? Is that possible?

Sub InsertRow()

  Dim ws As Worksheet
  Dim NBOFROWS As Range
  Set ws = ThisWorkbook.ActiveSheet

  With ws
    Set NBOFROWS = .Range("A2")
    ActiveCell.EntireRow.Offset(1).Resize(NBOFROWS.Value).Insert shift:=xlDown
  End With

End Sub

 

[rollis13]

Paste this updated version of your macro into the sheet's module:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2")) Is Nothing Or Range("A2") = "" Then Exit Sub
    Application.EnableEvents = False
    ActiveCell.EntireRow.Offset(8).Resize(Range("A2").Value).Insert Shift:=xlDown
    Application.EnableEvents = True
End Sub

Instead, if you need it to work on every sheet of your workbook paste it into the ThisWorkbook module and change its name to:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 

[Utradeshow]

That works amazing! I wanted to see if it would be easy to change the code to insert rows and copy down the formulas instead of just inserting rows starting from row 10 down

 

[rollis13]

If the formulas are in row 10 use this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2")) Is Nothing Or Range("A2") = "" Then Exit Sub
    Application.EnableEvents = False
    ActiveCell.EntireRow.Offset(7).Copy
    ActiveCell.EntireRow.Offset(8).Resize(Range("A2").Value).Insert Shift:=xlDown
    Application.CutCopyMode = False
    Application.EnableEvents = True
End Sub

 

[Utradeshow]

Yes Sir!! Thank you so much, that is exactly what I was looking for

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.

 

[Utradeshow]

I know this has been a while, But can this VBA be adjusted to copy down the formulas in the row above as well?

 

[rollis13]

to copy down the formulas in the row above as well

Please explain better; your formulas are in row 10, the macro copies the formula, inserts the new rows from row 11 pushing down whatever is already there.
What do you mean with "in the row above" ? which row are you referring to ?

 

[Utradeshow]

Yes, I have the first 10 rows, 1 Header row and 9 normal rows. The 9 rows contain formulas. When I enter say "100" in A2 the VBA inserts 100 rows after row 10, however, I lose all the formulas. Maybe if I could have it insert the 100 rows and then fill the formulas in?

 

[rollis13]

Oh, but this doesn't match with what I supposed in post #4 and post #8 so please attach a example of your cell layout and formulas with XL2BB LINK