Hi OssieMac,

It works perfectly. You have solved my problem. I cannot thank you more.
You are great! Looks like I have to enroll myself for Excel and Visual Basic
course soon...

Regards,
Eugene Wong

"OssieMac" wrote:

Hi again Eugene,


Open the workbook and select the required worksheet.

Delete any rows that you have inserted below the original row 9 and row 12
so that you have only the original rows displayed.

Press Alt/F11 to open the VBA editor.

If Module1 where you put the Initialize macro is not open then open it from
the Project Explorer in the left column. (Expand Modules and double click
Module1)

Delete all of the existing Initialize macro and then copy the new one below
and paste it in.

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"
Range("F9").Select
ActiveWorkbook.Names.Add Name:="Cell_F9", RefersToR1C1:=ActiveCell
ActiveWorkbook.Names.Add Name:="Cell_F9_Last", RefersToR1C1:=ActiveCell
Range("F12").Select
ActiveWorkbook.Names.Add Name:="Cell_F12", RefersToR1C1:=ActiveCell
ActiveWorkbook.Names.Add Name:="Cell_F12_Last", RefersToR1C1:=ActiveCell
Range("A16").Select
ActiveCell.FormulaR1C1 = _
"=SUM(Cell_F9:Cell_F9_Last)+SUM(Cell_F12:Cell_F12_ Last)"
MsgBox "Initialize has finished." & Chr(13) _
& "Click OK then close the VBA editor"
End Sub


Click anywhere within the module and press F5 to run it.

As before the above macro only has to be run once to initialize your
worksheet by naming ranges and cells.

You can now close the VBA editor.

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 where you inserted
the last macro.

Delete the entire existing macro and copy the one below and paste it in.



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
If insertRows < 1 Then
MsgBox "Cannot enter less than 2 for total rows." _
& Chr(13) & "No rows inserted." & Chr(13) & _
"Processing terminated."
Exit Sub 'Abort processing
End If
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

Range("Cell_F9").Offset(Cells(3, 1) - 1, 0).Select
ActiveWorkbook.Names.Add Name:="Cell_F9_Last", _
RefersToR1C1:=ActiveCell

End If

Case "$A$6"
'Following line determins number of rows to insert
insertRows = Target.Value - 1
If insertRows < 1 Then
MsgBox "Cannot enter less than 2 for total rows." _
& Chr(13) & "No rows inserted." & Chr(13) & _
"Processing terminated."
Exit Sub 'Abort processing
End If
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

Range("Cell_F12").Offset(Cells(6, 1) - 1, 0).Select
ActiveWorkbook.Names.Add Name:="Cell_F12_Last", _
RefersToR1C1:=ActiveCell


End If
End Select
Application.CutCopyMode = False

End Sub


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

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

Check that your formulas look right. Your formula in cell A16 now references
named ranges. It is modified by the macro.

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

I have also added some validation because if you try to enter a number less
than 2 which inserts one extra row, then it tries to enter zero or negative
number of rows and comes up an error.

Regards,

OssieMac