Controlling Row Inserts and Deletes Via Code

[ozzer]

Hi all,

I have a problem which I hope someone can help with.

I have a workbook which has two sheets.
Sheet one is used to list the assets and Liabilities for a person.
Sheet two is essentialy a copy of the details on one with a couple of
extra columns, with forumula linking back to sheet one. This sheet is
used to detmine a distribution of the assest. (the workbook is used to
calculate asset distribution for a divorce

The problem is is a user deletes a row from sheet one then on sheet 2
it will show a #REF error

Sheet one example

A1 contains the words House
B1 Contains the amount $250000
A2 Contains the words Car
B2 Contains the amount $40000


Sheet 2
A1 contains formula =sheet1!A1
B1 Contains formula = Sheet1!b1-c1-d1
A2 contains formula = Sheet1!A2
B2 contains formula = Sheet1!b2-c2-d2

What I want to do is have it so if a user wants to enter a new row on
sheet one between the current bits of data a new row will also be
entered into sheet two and the formula would also be entered.
Then if a user deletes a row from sheet one that same row on sheet two
must also be delted.
I was thiking I might use a custom toolbar for these commands. So a
user just has to click on sheet one where they want the new row (or the
row to delete) then click a button on the toolbar to inert or delete



Any assistance would be greatly appreciatred. I have tried this with a
macro but cant seem to get it to work.

Many thanks in advance

David

 

[damorrison]

to insert rows at the same timehighlite sheet one and then press ctrl
and then click onm sheet two, these sheets are now grouped together and
should do the what you do to sheet one to sheet two, you can groups
these together when you record your macro and then when you insert a
row in sheet one the same row will be inserted in sheet two before you
stop recording your macro be sure to ungroup the sheets, easiest way is
to right click on the sheet tab and choose ungroup.
when you are recording your macro, be sure to set the macro in relative
mode,click on the row number that you are already on, right click on
mouse and select insert, then go to the cell you want to end up at
ungroup your sheets and stop recording, since you recorded in relative
mode,
when you run the macro,anywhere your cell is that is the row that will
be inserted.
Hope you get through my explaination before you fall asleep, goodnight
all!!

 

[ozzer]

Thanks damorrison,
I shall endevour to give the macro another try. I am pretty ure though
I did exactly what you said already when I was recording the macro.
I would select bother sheets and insert the row.
in the code for the macro it was refering to an array(i think) of
("Assetts & Liability","Distribution") in its slect sheet line.
When I did the recording of the macro it would insert the two lines
into each sheet.
When I ran the macro it was only putting the new row on sheet 1
(assetts & liability)
this is why I was hoping for a code solution.

I have dabbled in code before however I would say I am "dangerous" with
code. As I kinda know what I am doing but not 100%.

Anyways thanks for the help.

 

[ozzer]

This is the code the macro was generating

It seems to insert a new row on sheet 1 however on sheet 2 it does not
insert a row. It overwrites the row

Sheets(Array("Sheet1", "Sheet2")).Select
Selection.EntireRow.Insert
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC-Sheet2!RC[1]-Sheet2!RC[2]"
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.Select

Hopefully someone can help with this?

 

[Norman Jones]

Hi Ozzer,

Try:

'=============>>
Public Sub Tester()
Dim rng As Range, rng2 As Range

Set rng = ActiveCell
Set rng2 = Sheets("Sheet2").Range(rng.Address)

rng.EntireRow.Insert
rng2.EntireRow.Insert
rng2(0, 1).FormulaR1C1 = "=Sheet1!RC"
rng2(0, 2).FormulaR1C1 = "=Sheet1!RC-Sheet2!RC[1]-Sheet2!RC[2]"

End Sub
'<<=============

 

[ozzer]

Thank you so so so so so so so much Norman
That is exactly what I needed.

Once again thanks

David