Insert Certain Number of Cells based on Value Input from Another Sheet

[AmyZZZZZZZ]

Hello, I am very new to VBA.
For my project I'm trying to insert certain number of rows based on a value input from another sheet.
For example, Sheet1 A1 input number is n
Then insert 2*(n-1) blank rows between row 1 and row 2.
Obviously this doesn't apply when n is equal or smaller than 1.
Could anyone help me with that? Thanks.

 

[Scott Huish]

Assuming that you want this to happen when the number is entered. I also guessed Sheet2 for where you wanted the rows inserted.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Long
Set c = Intersect(Target, Range("A1"))
If c Is Nothing Then Exit Sub
d = Range("A1")
If d > 1 Then Sheet2.Range("A2").Resize(2 * (d - 1)).EntireRow.Insert
End Sub

On the Sheet1 tab, right click and click on View Code
Paste the above code into the white area.
Hit ALT-Q
When you save the file, save it as type *.xlsm

 

[Fluff]

Hi & welcome to the board.
How about

Sub insertnrows()

    With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A1")
        If .Value > 1 Then
            Sheets("[COLOR=#ff0000]Test1[/COLOR]").Rows(2).Resize(2 * (.Value - 1)).Insert
        End If
    End With

End Sub

Changing sheet names in red to suit

 

[AmyZZZZZZZ]

Thank you!

 

[AmyZZZZZZZ]

Hi & welcome to the board.
How about

Sub insertnrows()

    With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A1")
        If .Value > 1 Then
            Sheets("[COLOR=#ff0000]Test1[/COLOR]").Rows(2).Resize(2 * (.Value - 1)).Insert
        End If
    End With

End Sub

Changing sheet names in red to suit

Hi Fluff, thanks. What if I want to insert between row 8 and 9 for example? could you explain what row(2) mean in ur code? thanks

 

[Scott Huish]

That's what row we're starting the insert on.

To start it between 8 and 9:

Sub insertnrows()

    With Sheets("Sheet1").Range("A1")
        If .Value > 1 Then
            Sheets("Test1").Rows(9).Resize(2 * (.Value - 1)).Insert
        End If
    End With

End Sub

 

[AmyZZZZZZZ]

That's what row we're starting the insert on.

To start it between 8 and 9:

Sub insertnrows()

    With Sheets("Sheet1").Range("A1")
        If .Value > 1 Then
            Sheets("Test1").Rows(9).Resize(2 * (.Value - 1)).Insert
        End If
    End With
End Sub

Thanks! One more question lol, is there a way to adjust the number of rows automatically when the value changes?
For example, i typed 2 then the total number of rows goes from 2 to 4; then i change the number to 3, the total number of rows now become 6.
Also, I actually have a column of inputs, each corresponds to different rows. Is there a way to address all of them?

Thanks.

 

[Scott Huish]

That would be like my first post:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Long
Set c = Intersect(Target, Range("A1"))
If c Is Nothing Then Exit Sub
d = Range("A1")
If d > 1 Then Sheet2.Rows(9).Resize(2 * (d - 1)).Insert
End Sub

 

[AmyZZZZZZZ]

That would be like my first post:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Long
Set c = Intersect(Target, Range("A1"))
If c Is Nothing Then Exit Sub
d = Range("A1")
If d > 1 Then Sheet2.Rows(9).Resize(2 * (d - 1)).Insert
End Sub

Thanks for typing it againhaha. When i press ALT-Q the sheet stays the same, but with private sub i couldn't click run.
Could you help me with that?

 

[Scott Huish]

You don't click run.
You need to put it in the Worksheet that you enter the row number on
On that sheet, right click the tab name and click View Code, paste in that white area.

When you change then number in A1, the rows will insert on Sheet2