Hi again Eugene,

My apologies for not getting back to you sooner but unfortunately it was
beyond my control.

Make sure that you back up your workbook before copying or running the
macros below.

Firstly I think that you need to change your formula in cells B9 and B12 to
absolute addressing. That is:-

B9 should be =$B$3
B12 should be =$B$6

If you are not familiar with absolute addressing, it is so that =B3 will not
become =B4, =B5 etc as the formula is copied down the page.

I am not sure if you need to change any others but after you run a test and
insert some rows, then check carefully that your formulas refer to the
correct cells.

When running tests, you can simply delete the inserted rows and re-run if
you want to.

The initialize macro you will need to run ONCE ONLY manually but the other
one will run automatically when you change the value in either A3 or A6. It
will only run for the cell you change so if you change A3 then the rows below
row 9 are inserted and if you change A6 the rows below row 12 (or what was
row 12 before inserting from row 9) will change.

You will get a message to confirm that you want to insert the rows. This is
needed in case someone makes a change by error (If the number of rows appears
wrong then accept it during the test stages and we will worry about that
later). Note even if the cell contains 6 and if you insert 6 again then that
is a change which will run the macro and insert additional rows. (Inserting
additional rows is OK if that is what you want to do.)

Instructions to copy and run the macros:

Open the workbook and select the required worksheet. (It is essential that
you have the required worksheet selected.)

Press Alt/F11 to open the VBA editor.

Select menu item Insert then click Module and a white area will appear on
the right of the screen.

Copy the following macro and paste it into the white area. (Copy from Sub
Initialize to End Sub)


Sub Initialize()
Sheets("Sheet1").Select
Rows("9:9").Select
ActiveWorkbook.Names.Add Name:="Row_9", RefersToR1C1:="=Sheet1!R9"
Rows("12:12").Select
ActiveWorkbook.Names.Add Name:="Row_12", RefersToR1C1:="=Sheet1!R12"
MsgBox "Initialize has finished." & Chr(13) _
& "Click OK then close the VBA editor"
End Sub


The above macro only has to be run ONCE to initialize your worksheet by
naming the existing rows 9 and 12. To run the macro while still in the VBA
editor:-

1. Click anywhere within the macro.
2. Press F5.

You can now close the VBA editor. (Click the X in the red box far top right
of screen)

You should now be back to the worksheet.
Right click on the worksheet name tab.
Select View code and you will be back in the VBA editor but this time if you
look at the project explorer down the left side you will see that the
selection is the worksheet name.

If a Private Sub €“ End Sub appears with nothing in between then delete both
these lines. (Dont panic if not there because that is OK.)

Copy the following macro and paste into the white space. (Copy from Private
Sub to End Sub)


Private Sub Worksheet_Change(ByVal Target As Range)
Dim row9 As Single
Dim row12 As Single
Dim insertRows As Single
Dim response As Variant

Select Case Target.Address
Case "$A$3"
'Following line determins number of rows to insert
insertRows = Target.Value - 1
response = MsgBox("Confirm that you want a total of " _
& Target.Value & " rows" & Chr(13) & _
"Cancel to abort", vbOKCancel)

If response = vbOK Then
row9 = Range("Row_9").Row
Rows(row9).Copy
Range(Rows(row9 + 1), _
Rows(row9 + insertRows)) _
.Insert Shift:=xlDown
End If

Case "$A$6"
'Following line determins number of rows to insert
insertRows = Target.Value - 1
response = MsgBox("Confirm that you want a total of " _
& Target.Value & " rows" & Chr(13) & _
"Cancel to abort", vbOKCancel)

If response = vbOK Then
row12 = Range("Row_12").Row
Rows(row12).Copy
Range(Rows(row12 + 1), _
Rows(row12 + insertRows)) _
.Insert Shift:=xlDown
End If
End Select

End Sub



You can now close the VBA editor and you should be back at your worksheet.

If you have not already done so then edit cells B9 and B12 so that they have
absolute references as per instructions above.

Change the value in cell A3 and your first set of lines should be inserted.
Repeat for cell A6.

Note that it inserts one less line than the number entered because if I
understand correctly, the total number of lines required includes the
original.

Check that your formulas look right.

Writing the instructions feel like writing a book but I like to feel
confident that I have included everything.

Feel free to get back to me if you need any changes. However, if you do then
please include the version of Excel are you using?

Regards,

OssieMac