Updating Code when a row is inserted

[ML]

Hi I have a lot of VBA code in a budget spread sheet.
When a person inserts a row, I need my macros to somehow update dynamically.

One "small" example would be on my calculate worksheet event
If the user inserts a row, at 127 for example, then the value of cell C5 is
lost.
Is there a way to C127 become C128 if a row is inserted?

Thank you,
Mark

Private Sub Worksheet_Calculate()

'Extra Cash Cells in Row 5 = Extra Cash Cells in Row 127
Range("C5").Value = Range("C127")
Range("D5").Value = Range("D127")
Range("E5").Value = Range("E127")
Range("F5").Value = Range("F127")
Range("G5").Value = Range("G127")

 

[Joel]

The best way to accomplish what you want is to use define names on the
worksheet. Go to worksheet menu Insert - Names - Define.

You can define the following cells
abc as C5
xyz as C127

Then in VBA
Range("abc") = Range ("xyz")

When rows are deleted the workjsheet will automatically update the define
range and VBA will also be updated.

 

[ML]

Hi Joel
Thank you for the help!
I was may aware that I can use named ranges.
I have found that is this specific scenario that I posted; I did not even
need to name a range because it is all formula based, so I simply told C5 to
Sum C127 and so on.
That was an easy fix.

Now, let's suppose I did it the way that you suggested;
abc as C5
xyz as C127

Then in VBA
Range("abc") = Range ("xyz")

THat would mean that I would have to give a named range to each cell?
C5 being (abc) and C127 being (xyz) in order for the insert a new row to
work dynaically with my code?

That seems like an awful lot of names to have to create.

There must be a way that when a row is inserted, to tell the macro to
increment +1

I would imaine through a For Each rw loop??

Mark

If there a method

 

[Joel]

Define cell C127 on worksheet to be SourceData (or similar).

Private Sub Worksheet_Calculate()

'Extra Cash Cells in Row 5 = Extra Cash Cells in Row 127
For Col = 0 to 4
Range("C5").offset(rowoffset:=0,columnoffset:=Col).Value = _
Range("SourceData").offset(rowoffset:=0,columnoffset:=Col)
next col

 

[ML]

Ahhh,
We are definetely getting closer to what I am looking for, only I would do
the reverse and use rows instead of columns.Also I don't think we need to use
the full word in the array when doing offsetts

Dim rw as Integer

For rw = 6 to 58
Range("C" & rw).offset.Value = _
Range("C127").offset(rw,0)
next rw

Hummmm, what do you think?

Mark

 

[Joel]

You have a number of problems with this code.
1) remove offset from Range("C" & rw).offset.Value
2) subtract 6 from row offset Range("C127").offset(rw - 6,0)
3) It still doesn't solve your real problem. If a new row is inserted
between rows 58 and 127 you are copying an empty row. If you add a define
cell then when the row is inserted the define automatically gets updated.

 

[ML]

You have a number of problems with this code.
1) remove offset from Range("C" & rw).offset.Value
2) subtract 6 from row offset Range("C127").offset(rw - 6,0)
3) It still doesn't solve your real problem. If a new row is inserted
between rows 58 and 127 you are copying an empty row. If you add a define
cell then when the row is inserted the define automatically gets updated.

 

[Joel]

There was no response in this posting????

 

[ML]

Hey Joe
As I said, ofr this case, I think I am OK just by using formaulas and cell
links but I have some other things that may require a method more like this
one that you posted

I think I need an insertrow boolean function
That may help for some other things

I am actually "mostly" an AutoCAD VBA programmer with can get very involved,
however I do like to dabble with Excel as well.
I love Excel
Thank you
Mark