VBA Syntax to insert rows from a cell value

[sabrejew]

Hello all,

New poster but long time reader.

I have an Excel 2010 spreadsheet that i am trying to create a VBA code to do some specific things. I am not totally familiar with VBA but do know enough to be dangerous.

Scenario:

Sheet 1 has a cell (say C3) that has a number that can be typed in. For argument, we are going to say this # is 3 which indicates 3 locations.

On sheet 2, i would like for the code to automatically insert 8 lines per location listed in Sheet1!C3. I this example, there would be 24 lines inserted into Sheet 2. We are going to say that these lines need to be added starting at row 10.

Is there a way to make this a live syntax that adds or removes groups of 8 lines as the number changes in Sheet1!C3 without having to create a button or physically run the Macro?

I am completely lost in trying to figure this one out. The simple items that can be obtained from the record macro feature are easy. I just dont know where to start on the insert lines based from the # of locations listed.

Thank you in advance for any help.

 

[My Aswer Is This]

Try this:

This script will run when you enter a number in Sheet(1).Range("C3")

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the Sheet(1) 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  4/16/2019  4:03:13 AM  EDT
On Error GoTo M
If Target.Address = Range("C3").Address Then
Dim ans As Long
ans = Target.Value * 8
Sheets(2).Rows(10).Resize(ans).Insert
End If
Exit Sub
M:
MsgBox "You enter  " & Target.Value & "  This is not a proper number"
End Sub

 

[sabrejew]

Thank you for the quick response. I will give this a try. Thank you for the help.